Il tutorial spiega i dadi e i bulloni delle funzioni MESE ed EOMONTH di Excel. Troverai una serie di esempi di formule che dimostrano come estrarre il mese dalla data in Excel, ottenere il primo e l’ultimo giorno del mese, convertire il nome del mese in numero e altro ancora.
Nel precedente articolo, abbiamo esplorato una varietà di funzioni di Excel per lavorare con giorni feriali e giorni dell’anno., Oggi, ci accingiamo a operare su un’unità di tempo più grande e imparare le funzioni che Microsoft Excel fornisce per mesi.,mese numero, data in Excel
Excel MESE di funzione – sintassi e usi
Microsoft Excel fornisce un MESE speciale funzione per estrarre un mese dalla data di che restituisce il numero del mese che vanno da 1 (gennaio) al 12 (dicembre).,
La funzione MONTH può essere utilizzata in tutte le versioni di Excel 2016 – 2000 e la sua sintassi è così semplice come può essere:
Dove serial_number è una data valida del mese che stai cercando di trovare.
Per il corretto funzionamento delle formule del MESE di Excel, è necessario inserire una data utilizzando la funzione DATA(anno, mese, giorno). Ad esempio, la formula =MONTH(DATE(2015,3,1)) restituisce 3 poiché la DATA rappresenta il 1 ° giorno di marzo 2015.,
Formule come=MONTH("1-Mar-2015") funzionano bene, anche se possono verificarsi problemi in scenari più complessi se le date vengono inserite come testo.
In pratica, invece di specificare una data all’interno della funzione MESE, è più conveniente fare riferimento a una cella con una data o fornire una data restituita da qualche altra funzione. Ad esempio:
=MONTH(A1) – restituisce il mese di una data nella cella A1.
=MONTH(TODAY()) – restituisce il numero del mese corrente.
A prima vista, la funzione MESE di Excel potrebbe apparire semplice., Ma guardare attraverso gli esempi qui sotto e sarete stupiti di sapere quante cose utili si può effettivamente fare.
Come ottenere il numero del mese dalla data in Excel
Ci sono diversi modi per ottenere il mese dalla data in Excel. Quale scegliere dipende esattamente dal risultato che stai cercando di ottenere.
- Funzione MESE in Excel-ottieni il numero del mese dalla data.
Questo è il modo più ovvio e più semplice per convertire la data in mese in Excel. Ad esempio:
-
=MONTH(A2)– restituisce il mese di una data nella cella A2., -
=MONTH(DATE(2015,4,15))– restituisce 4 corrispondente ad aprile. -
=MONTH("15-Apr-2015")– ovviamente, restituisce anche il numero 4.
-
- Funzione di TESTO in Excel-estrai mese come stringa di testo.
Un modo alternativo per ottenere un numero di mese da una data Excel utilizza la funzione di TESTO:
-
=TEXT(A2, "m")– restituisce un numero di mese senza uno zero iniziale, come 1 – 12. -
=TEXT(A2,"mm")– restituisce un numero di mese con uno zero iniziale, come 01 – 12.,
Fai molta attenzione quando usi le formule di TESTO, perché restituiscono sempre i numeri del mese come stringhe di testo. Quindi, se si prevede di eseguire ulteriori calcoli o utilizzare i numeri restituiti in altre formule, è meglio attenersi alla funzione MESE di Excel.
-
La seguente schermata mostra i risultati restituiti da tutte le formule di cui sopra. Si noti il giusto allineamento dei numeri restituiti dalla funzione MESE (celle C2 e C3) rispetto ai valori di testo allineati a sinistra restituiti dalle funzioni di TESTO (celle C4 e C5).,
Come estrarre il nome del mese dalla data in Excel
Nel caso in cui si desideri ottenere un nome del mese anziché un numero, si utilizza nuovamente la funzione TEXT, ma con un codice data diverso:
-
=TEXT(A2, "mmm")– restituisce un nome del mese abbreviato, come Jan – Dic. -
=TEXT(A2,"mmmm")– restituisce un nome completo del mese, come gennaio – dicembre.,
Se in realtà non si desidera convertire la data in mese nel foglio di lavoro di Excel, si desidera visualizzare solo un nome di mese anziché la data completa, quindi non si desidera alcuna formula.
Selezionare una cella(s) con date, premere Ctrl+1 per aprire la finestra di dialogo Formato celle. Nella scheda Numero, selezionare Personalizzato e digitare ” mmm ” o “mmmm” nella casella Tipo per visualizzare rispettivamente i nomi dei mesi abbreviati o completi. In questo caso, le voci rimarranno date di Excel completamente funzionali che è possibile utilizzare nei calcoli e in altre formule., Per ulteriori dettagli sulla modifica del formato della data, vedere Creazione di un formato di data personalizzato in Excel.
Come convertire il numero del mese in nome del mese in Excel
Supponiamo di avere un elenco di numeri (da 1 a 12) nel foglio di lavoro di Excel che si desidera convertire in nomi di mese. Per fare ciò, puoi utilizzare una delle seguenti formule:
Come funzionano queste formule
Come convertire il nome del mese in numero in Excel
Ci sono due funzioni di Excel che possono aiutarti a convertire i nomi dei mesi in numeri: DATEVALUE e MONTH., La funzione DATEVALUE di Excel converte una data memorizzata come testo in un numero di serie che Microsoft Excel riconosce come data. E poi, la funzione MESE estrae un numero di mese da quella data.
La formula completa è la seguente:
=MONTH(DATEVALUE(A2 & "1"))
Dove A2 in una cella contenente il nome del mese che si desidera trasformare in un numero (&”1″ viene aggiunto per la funzione DATEVALUE capire è una data).,
Come ottenere l’ultimo giorno del mese in Excel (funzione EOMONTH)
La funzione EOMONTH in Excel viene utilizzata per restituire l’ultimo giorno del mese in base alla data di inizio specificata. Ha i seguenti argomenti, entrambi necessari:
- Start_date – la data di inizio o un riferimento a una cella con la data di inizio.
- Mesi-il numero di mesi prima o dopo la data di inizio. Utilizzare un valore positivo per le date future e un valore negativo per le date passate.,
Ecco alcuni esempi di formule di EOMONTH:
=EOMONTH(A2, 1) – restituisce l’ultimo giorno del mese, un mese dopo la data nella cella A2.
=EOMONTH(A2, -1) – restituisce l’ultimo giorno del mese, un mese prima della data nella cella A2.
Invece di un riferimento di cella, puoi codificare una data nella tua formula EOMONTH. Ad esempio, entrambe le formule seguenti restituiscono l’ultimo giorno di aprile.,
=EOMONTH("15-Apr-2015", 0)
=EOMONTH(DATE(2015,4,15), 0)
Per restituire l’ultimo giorno del mese corrente, si utilizza la funzione TODAY() nel primo argomento della formula EOMONTH in modo che la data odierna sia presa come data di inizio. E, inserisci 0 nell’argomentomonths perché non vuoi cambiare il mese in entrambi i casi.
=EOMONTH(TODAY(), 0)
Ed ecco i risultati restituiti dalla Excel EOMONTH formule discusso in precedenza:
Se si desidera calcolare quanti giorni mancano fino alla fine del mese corrente, è sufficiente sottrarre la data restituita da OGGI() dalla data restituito da EOMONTH e applicare il formato Generale di una cella:
=EOMONTH(TODAY(), 0)-TODAY()
Come trovare il primo giorno del mese in Excel
Come già sapete, Microsoft Excel fornisce solo una funzione per restituire l’ultimo giorno del mese (EOMONTH)., Quando si tratta del primo giorno del mese, c’è più di un modo per ottenerlo.
Esempio 1. Ottieni il 1 ° giorno del mese per il numero del mese
Se hai il numero del mese, usa una semplice formula di DATA come questa:
Ad esempio, =DATE(2015, 4, 1) restituirà 1-Apr-15.
Se i tuoi numeri si trovano in una determinata colonna, ad esempio nella colonna A, puoi aggiungere un riferimento di cella direttamente nella formula:
=DATE(2015, B2, 1)
Esempio 2., Ottenere il 1 ° giorno del mese da una data
Se si desidera calcolare il primo giorno del mese basato sulla data, è possibile utilizzare Excel DATA la funzione di nuovo, ma questa volta è necessario anche il MESE di funzione per estrarre il numero del mese:
Per esempio, la seguente formula restituisce il primo giorno del mese basato sulla data nella cella A2:
=DATE(2015,MONTH(A2),1)
Esempio 3., Trova il primo giorno del mese in base alla data corrente
Quando i tuoi calcoli sono basati sulla data odierna, usa un collegamento delle funzioni Excel EOMONTH e TODAY:
=EOMONTH(TODAY(),0) +1 – restituisce il 1 ° giorno del mese successivo.
Come ricordi, abbiamo già usato una formula EOMONTH simile per ottenere l’ultimo giorno del mese corrente. E ora, è sufficiente aggiungere 1 a quella formula per ottenere il primo giorno del mese successivo.,
In modo simile, è possibile ottenere il primo giorno del mese precedente e corrente:
=EOMONTH(TODAY(),-2) +1 – restituisce il 1 ° giorno del mese precedente.
=EOMONTH(TODAY(),-1) +1 – restituisce il 1 ° giorno del mese corrente.
È inoltre possibile utilizzare la funzione DATA di Excel per gestire questa attività, anche se le formule sarebbero un po ‘ più lunghe. Ad esempio, indovina cosa fa la seguente formula?
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
Sì, restituisce il primo giorno del mese corrente.
E come lo costringi a restituire il primo giorno del mese successivo o precedente?, Giù le mani 🙂 Basta aggiungere o sottrarre 1 da/per il mese corrente:
Per tornare al primo giorno del mese seguente:
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)
Per tornare al primo giorno del mese precedente:
=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)
Come calcolare il numero di giorni in un mese
In Microsoft Excel, esistono una varietà di funzioni per lavorare con date e orari. Tuttavia, manca una funzione per calcolare il numero di giorni in un dato mese. Quindi, dovremo compensare quell’omissione con le nostre formule.
Esempio 1., Per ottenere il numero di giorni mese numero
Se si conosce il numero del mese, il GIORNO seguente / DATA di formula restituisce il numero di giorni del mese:
Nella formula sopra riportata, la DATA la funzione restituisce il primo giorno del mese successivo, da cui sottrarre 1 per ottenere l’ultimo giorno del mese in cui si desidera. E poi, la funzione GIORNO converte la data in un numero di giorno.
Ad esempio, la seguente formula restituisce il numero di giorni di aprile (il 4 ° mese dell’anno).,
=DAY(DATE(2015, 4 +1, 1) -1)
Esempio 2. Per ottenere il numero di giorni in un mese in base alla data
Se non si conosce un numero di mese ma si dispone di una data entro quel mese, è possibile utilizzare le funzioni ANNO e MESE per estrarre il numero di anno e mese dalla data. Basta incorporarli nella formula GIORNO / DATA discussa nell’esempio precedente e ti dirà quanti giorni contiene un determinato mese:
=DAY(DATE(YEAR(A2), MONTH(A2) +1, 1) -1)
Dove A2 è la cella con una data.
In alternativa, puoi usare una formula DAY / EOMONTH molto più semplice., Come ricorderete, Excel EOMONTH funzione restituisce l’ultimo giorno del mese, in modo da non avete bisogno di ulteriori calcoli:
=DAY(EOMONTH(A1, 0))
La schermata riportata di seguito viene illustrato il risultato restituito da tutte le formule, e come potete vedere sono identici:
Come somma dei dati per mese in Excel
In una tabella di grandi dimensioni con un sacco di dati, spesso c’è la necessità di ottenere una somma di valori per un determinato mese. E questo potrebbe essere un problema se i dati non sono stati inseriti in ordine cronologico.,
La soluzione più semplice è aggiungere una colonna helper con una semplice formula MESE Excel che convertirà le date in numeri di mese. Dì, se le tue date sono nella colonna A, usi = MONTH(A2).
E ora, annota un elenco di numeri (da 1 a 12, o solo quei numeri mensili che ti interessano) in una colonna vuota e somma i valori per ogni mese usando una formula SUMIF simile a questa:
=SUMIF(C2:C15, E2, B2:B15)
Dove E2 è il numero del mese.,
La seguente schermata mostra il risultato dei calcoli:
Se preferisci non aggiungere una colonna helper al tuo foglio Excel, nessun problema, puoi farne a meno. Una funzione SUMPRODUCT un po ‘ più complicata funzionerà un trattamento:
=SUMPRODUCT((MONTH($A$2:$A$15)=$E2) * ($B$2:$B$15))
Dove la colonna A contiene le date, la colonna B contiene i valori da sommare e E2 è il numero del mese.
Come formattare condizionatamente le date in base al mese
Ora che sai come utilizzare le funzioni di Excel MONTH e EOMONTH per eseguire vari calcoli nei tuoi fogli di lavoro, puoi fare un ulteriore passo avanti e migliorare la presentazione visiva. Per questo, utilizzeremo le funzionalità di formattazione condizionale di Excel per le date.,
Oltre agli esempi forniti nell’articolo sopra menzionato, ora ti mostrerò come puoi evidenziare rapidamente tutte le celle o intere righe relative a un determinato mese.
Esempio 1. Evidenzia le date nel mese corrente
Nella tabella dell’esempio precedente, supponiamo di voler evidenziare tutte le righe con le date del mese corrente.
Prima di tutto, estrai i numeri del mese dalle date nella colonna A usando la formula più semplice =MONTH (A A2). E poi, si confrontano quei numeri con il mese corrente restituito da = MESE (OGGI ())., Di conseguenza, hai la seguente formula che restituisce TRUE se i numeri dei mesi corrispondono, FALSE altrimenti:
=MONTH($A2)=MONTH(TODAY())
Crea una regola di formattazione condizionale di Excel basata su questa formula e il tuo risultato potrebbe assomigliare allo screenshot qui sotto (l’articolo è stato scritto in aprile, quindi tutte le date di aprile sono evidenziate).
Esempio 2. Evidenziando le date per mese e giorno
Ed ecco un’altra sfida. Supponiamo che tu voglia evidenziare le principali festività nel tuo foglio di lavoro indipendentemente dall’anno. Diciamo giorni di Natale e Capodanno., Come ti avvicineresti a questo compito?
Semplicemente utilizzare Excel in funzione per estrarre il giorno del mese (1 – 31) e la funzione MESE per ottenere il numero del mese, e quindi verificare se il GIORNO è uguale a 25 o il 31, e se il MESE è pari a 12:
=AND(OR(DAY($A2)=25, DAY($A2)=31), MONTH(A2)=12)
Questo è quanto, il MESE della funzione in Excel non funziona. Sembra essere molto più versatile di quanto sembri, eh?
In un paio dei prossimi post, calcoleremo settimane e anni e speriamo che imparerai alcuni trucchi più utili., Se siete interessati a unità di tempo più piccole, si prega di controllare le parti precedenti della nostra serie di date Excel (troverete i link qui sotto). Vi ringrazio per la lettura e spero di vedervi la prossima settimana!
- Come cambiare il formato della data in Excel e creare formati di data
- Come creare un calendario in Excel (discesa e stampabile)
- Come inserire le date in Excel
- Come convertire il testo in data in Excel
- la Conversione di Excel date in formato testo
- di Lavoro con giorni feriali e giorni del mese in Excel