Excel: conversione di date in quarti
Excel dispone di funzioni integrate per la restituzione di anno, numero del mese, nome del mese, numero della settimana, giorno della settimana o giorno da qualsiasi cella che contiene una data. Ma non c’è un modo integrato per restituire un quarto di numero. I trimestri sono più difficili perché la definizione di un trimestre varia a seconda della data di fine dell’anno fiscale.
PER GLI ANNI CIVILI
Se l’esercizio finanziario termina il 31 dicembre, è possibile utilizzare una formula breve. (Figura 1 mostra le funzioni di Excel che è possibile utilizzare per convertire una data in anni, mesi, settimane o giorni feriali.) Innanzitutto, usa la funzione = MONTH () per convertire la data in un numero di mese. Dividi quel risultato per 3. Le date di gennaio restituiranno 0.33, 0.67 febbraio, 1.00 marzo, 1.33 aprile e così via. Usando la funzione = ROUNDUP (), avrai 1 per gennaio, febbraio e marzo; 2 per aprile a giugno; e così via.
Per mostrare il trimestre come ” Q1 ” o “Qtr 1”, concatenare il testo appropriato prima del risultato. Nella Figura 2, la cella A7 utilizza una formula di = “Q” &ROUNDUP(MONTH (A2)/3,0)&”-“&ANNO (A2) per tornare ” Q1-2017.”
PER GLI ALTRI ESERCIZI FISCALI
Gli esercizi fiscali che terminano il 31 gennaio sono comuni nelle società di vendita al dettaglio. Gli appaltatori governativi potrebbero utilizzare una fine dell’anno fiscale di settembre 30. Per calcolare un trimestre fiscale, prima convertire la data in un numero di mese usando = MONTH (). Questo risultato è limitato a 12 interi da 1 a 12.
È possibile utilizzare una formula VLOOKUP per convertire il numero del mese in numero del quarto. Ma la formula =VLOOKUP (MESE (A2),{1,4;2,1;5,2;8,3;11,4},2) è difficile da ricordare e digitare. La costante dell’array all’interno delle parentesi graffe contiene una tabella di ricerca a 5 righe per 2 colonne.
Invece, la funzione =CHOOSE() consente di specificare 12 numeri di trimestre che corrispondono ai mesi da 1 a 12. Se il tuo anno fiscale termina a gennaio, useresti = CHOOSE(MONTH (A2),4,1,1,1,2,2,2,3,3,3,4,4). Per un anno fiscale che termina a marzo, utilizzare = CHOOSE(MONTH (A2),4,4,4,1,1,1,2,2,2,3,3,3).
Se si preferisce mostrare il quarto come Q1 invece di 1, è possibile utilizzare la e commerciale per concatenare un “Q” prima della funzione CHOOSE. I risultati sono mostrati nelle celle A13 e A20 della Figura 2, con le formule mostrate in D13 e D20.
AGGIUNTA DI UN ANNO FISCALE AL TRIMESTRE
In alcune società, l’anno fiscale è determinato dall’anno civile in cui si verifica il 12 ° mese dell’anno. Ad esempio, un anno fiscale che va da febbraio 2017 a gennaio 2018 avrebbe trimestri chiamati Q1-2018 attraverso Q4-2018. In altre società, l’anno fiscale potrebbe essere indicato come qualcosa come ” 17-18.”
Nel caso in cui l’anno fiscale sia l’anno del mese finale, utilizzare una funzione IF() per verificare se il numero del mese è inferiore al mese iniziale. Ad esempio, con un anno fiscale che termina a gennaio, l’anno fiscale sarà =ANNO(A2) se la data è precedente a febbraio e =ANNO(A2)+1 a febbraio e versioni successive. Pertanto, per includere l’anno fiscale con il trimestre, è possibile utilizzare =IF(MONTH(A2)<2,0,1)+YEAR(A2).
Per un anno che termina a marzo, cambi il test <2 in <4: =IF(MONTH(A2)< 4,0,1)+YEAR(A2). Per un anno che termina a settembre, dovresti testare <10: = IF(MONTH(A2)<10,0,1)+YEAR (A2).
La cella A21 nella Figura 2 mostra come concatenare il trimestre fiscale e l’anno insieme usando questa formula lunga: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-“&IF(MONTH(A17)<2,0,1)+YEAR(A17). Ciò restituirebbe il trimestre in un formato di ” Q4-2017.”
Se, invece, ne hai bisogno per dire 17-18, la formula lunga diventa ancora più lunga. Solo per ottenere la parte dell’anno della formula, useresti: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).
RESTITUIRE UN PERIODO INVECE DI UN MESE
Recentemente ho condotto un seminario per IMA® a Nashville. Poiché il governo dello stato del Tennessee ha un anno fiscale che termina a giugno, hanno preferito segnalare utilizzando un numero di periodo invece di un mese. Luglio è il periodo 1, dicembre è il periodo 6, gennaio è il periodo 7 e così via. In questo caso, molti colleghi hanno utilizzato una tabella VLOOKUP sul lato destro dei dati. Ma potrebbero anche usare =CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6) per ottenere lo stesso risultato.
LE DATE NON POSSONO ESSERE TEXT
L’avvertenza per tutte queste formule è che stai memorizzando le date come date reali e non come testo. Un modo rapido per controllare: Premere Ctrl e il tasto Accento Grave ( ` ) per passare alla modalità Mostra formule. Le date reali verranno convertite in un numero di serie come 42783. Se vedi ancora una data di testo, le formule non funzioneranno. Premere di nuovo Ctrl` ‘ per uscire da Mostra formule.
Recentemente mi sono imbattuto in un foglio di lavoro in cui il manager voleva visualizzare le date come 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. I suffissi dopo la data (“st”,” nd”,” rd “e” th”) convertono un numero cardinale in un numero ordinale. Mentre la maniglia di riempimento di Excel ti consentirà di creare facilmente una colonna di numeri cardinali, le formule in questo articolo diventano molto più complesse. Una formula di 41 caratteri è cresciuta in 2.438 caratteri per gestire le date del testo (vedi Figura 3).
