Sommario
È possibile impostare la griglia del calendario dinamico su un foglio di lavoro Excel con una serie di formule, come spiegato in questo articolo. Nell'esempio mostrato, la formula in B6 è:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
dove "inizio" è l'intervallo denominato K5 e contiene la data 1 settembre 2018.
Spiegazione
Nota: questo esempio presuppone che la data di inizio venga fornita come la prima del mese. Vedi sotto per una formula che restituirà dinamicamente il primo giorno del mese corrente.
Con il layout della griglia come mostrato, il problema principale è calcolare la data nella prima cella del calendario (B6). Questo viene fatto con questa formula:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Questa formula calcola la domenica prima del primo giorno del mese utilizzando la funzione SCEGLI per "ripristinare" il numero corretto di giorni alla domenica precedente. SCEGLI funziona perfettamente in questa situazione, perché consente valori arbitrari per ogni giorno della settimana. Usiamo questa funzione per ripristinare zero giorni quando il primo giorno del mese è una domenica. Ulteriori dettagli su questo problema sono forniti qui.
Con il primo giorno stabilito in B6, le altre formule nella griglia incrementano semplicemente la data precedente di uno, iniziando con la formula in C6:
=IF(B6"",B6,$H5)+1
Questa formula verifica la cella immediatamente a sinistra per un valore. Se non viene trovato alcun valore, estrae un valore dalla colonna H nella riga sopra. Nota $ H5 è un riferimento misto, per bloccare la colonna mentre la formula viene copiata in tutta la griglia. La stessa formula viene utilizzata in tutte le celle tranne B6.
Regole di formattazione condizionale
Il calendario utilizza formule di formattazione condizionale per modificare la formattazione per ombreggiare i mesi precedenti e futuri e per evidenziare il giorno corrente. Entrambe le regole vengono applicate all'intera griglia. Per i mesi precedenti e successivi, la formula è:
=MONTH(B6)MONTH(start)
Per il giorno corrente, la formula è:
=B6=TODAY()
Per ulteriori dettagli, vedere: formattazione condizionale con formule (10 esempi)
Intestazione del calendario
Il titolo del calendario, mese e anno, viene calcolato con questa formula nella cella B4:
=start
Formattato con il formato numerico personalizzato "mmmm aaaa". Per centrare il titolo sopra il calendario, l'intervallo B4: H4 ha l'allineamento orizzontale impostato su "centra la selezione". Questa è un'opzione migliore rispetto all'unione delle celle, poiché non altera la struttura della griglia nel foglio di lavoro.
Calendario perpetuo con data corrente
Per creare un calendario che si aggiorna automaticamente in base alla data corrente, puoi utilizzare una formula come questa in K5:
=EOMONTH(TODAY(),-1)+1
Questa formula ottiene la data corrente con la funzione TODAY, quindi ottiene il primo giorno del mese corrente utilizzando la funzione EOMONTH. Sostituisci OGGI () con una data specifica per creare un calendario in un mese diverso. Maggiori dettagli su come funziona EOMONTH qui.
Passaggi per creare
- Nascondi le linee della griglia (opzionale)
- Aggiungi un bordo a B5: H11 (7R x 7C)
- Assegna a K5 il nome "inizio" e inserisci la data come "1 settembre 2018"
- Formula in B4 = inizio
- Formatta B4 come "mmmm aaaa"
- Seleziona B4: H4, imposta l'allineamento su "Centra la selezione"
- Nell'intervallo B5: H5, inserisci le abbreviazioni dei giorni (SMTWTFS)
- Formula in B6 = start-CHOOSE (WEEKDAY (start), 0,1,2,3,4,5,6)
- Seleziona B6: H11, applica il formato numerico personalizzato "d"
- Formula in C6 = IF (B6 "", B6, $ H5) +1
- Copia la formula in C6 nelle celle rimanenti nella griglia del calendario
- Aggiungi regola di formattazione condizionale precedente / successiva (vedi formula sopra)
- Aggiungi regola di formattazione condizionale corrente (vedi formula sopra)
- Cambia la data in K5 con un'altra data del "primo del mese" per eseguire il test
- Per il calendario perpetuo, formula in K5 = EOMONTH (TODAY (), - 1) +1
