Tutorial Excel: Calcolo di date e ore (calcolare differenze, età, aggiungere/sottrarre)
In Excel, il calcolo delle Data e ora viene utilizzato frequentemente, ad esempio per calcolare la differenza tra due date/orari, aggiungere o sottrarre date e ore, calcolare l'età in base alla data di nascita e così via. In questo tutorial sono elencati quasi tutti gli scenari relativi al calcolo di date e ore e vengono forniti i metodi corrispondenti.
In questo tutorial vengono presentati alcuni esempi per illustrare i metodi; puoi personalizzare i riferimenti in base alle tue esigenze quando utilizzi i codici VBA o le formule riportati di seguito.
1. Calcola la differenza tra due date o orari
Calcolare la differenza tra due date o due orari è probabilmente l’operazione più comune quando si lavora con date e ore in Excel. Seguire gli esempi riportati di seguito ti aiuterà a risolvere rapidamente queste situazioni e a lavorare con maggiore efficienza.
1,11 Calcolare la differenza tra due date in giorni/mesi/anni/settimane
La funzione DATEDIF di Excel permette di calcolare in modo rapido la differenza tra due date in giorni, mesi, anni o settimane.
Fare clic per ulteriori dettagli sulla funzione DATEDIF
Differenza in giorni tra due date
Per ottenere la differenza in giorni tra due date nelle celle A2 e B2, utilizzare la seguente formula:
=DATEDIF(A2,B2,«d»)
Premere il tasto Invio per ottenere il risultato.
Differenza in mesi tra due date
Per ottenere la differenza in mesi tra due date nelle celle A5 e B5, utilizzare la seguente formula:
=DATEDIF(A5,B5,«m»)
Premere il tasto Invio per ottenere il risultato.
Differenza in anni tra due date
Per ottenere la differenza in anni tra due date nelle celle A8 e B8, utilizzare la seguente formula:
=DATEDIF(A8,B8,«y»)
Premere il tasto Invio per ottenere il risultato.
Differenza in settimane tra due date
Per ottenere la differenza in settimane tra due date nelle celle A11 e B11, utilizzare la seguente formula:
=DATEDIF(A11,B11,«d»)/7
Premere il tasto Invio per ottenere il risultato.
Nota:
1) Quando si utilizza la formula sopra per calcolare la differenza in settimane, il risultato potrebbe apparire in formato data; in tal caso, formattarlo come Generale o Numero a seconda delle esigenze.
2) Quando si utilizza la formula sopra per calcolare la differenza in settimane, il risultato potrebbe essere un numero decimale. Per ottenere il numero intero di settimane, è possibile anteporre la funzione ARROTONDA.PER.DIF., come mostrato di seguito, e ricavare così la differenza intera in settimane:
=ROUNDDOWN(DATEDIF(A11,B11,«d»)/7,0)
1,12 Calcola i mesi ignorando anni e giorni tra due date
Se si desidera calcolare esclusivamente la differenza in mesi tra due date, ignorando anni e giorni, come illustrato nell’immagine seguente, è disponibile una formula appositamente pensata per questo scopo.
=DATEDIF(A2,B2,«ym»)
Premere il tasto Invio per ottenere il risultato.
A2 è la data di inizio e B2 è la data di fine.
1,13 Calcola i giorni ignorando anni e mesi tra due date
Se si desidera calcolare esclusivamente la differenza in giorni tra due date, ignorando anni e mesi come illustrato nell’immagine seguente, è disponibile una formula appositamente pensata per questo scopo.
=DATEDIF(A5,B5,«md»)
Premere il tasto Invio per ottenere il risultato.
A5 è la data di inizio e B5 è la data di fine.
1,14 Calcolare la differenza tra due date e restituire anni, mesi e giorni
Se desideri calcolare la differenza tra due date e visualizzarla nel formato xx anni, xx mesi e xx giorni, come nell’immagine seguente, è disponibile anche una formula apposita.
=DATEDIF(A8, B8, «y») &« years, »&DATEDIF(A8, B8, «ym») &« months, » &DATEDIF(A8, B8, «md») &« days»
Premere il tasto Invio per ottenere il risultato.
A8 è la data di inizio e B8 è la data di fine.
1,15 Calcolare la differenza tra una data e oggi
Per calcolare automaticamente la differenza tra una data e oggi, basta sostituire la data finale nelle formule precedenti con OGGI(). Di seguito un esempio che mostra come calcolare in giorni la differenza tra una data passata e oggi.
=DATEDIF(A11,TODAY(),«d»)
Premere il tasto Invio per ottenere il risultato.
Nota: se si desidera calcolare la differenza tra una data futura e oggi, impostare oggi come data iniziale e la data futura come data finale, come segue:
=DATEDIF(TODAY(),A14,«d»)
Si noti che nella funzione DATEDIF la data iniziale deve precedere la data finale; in caso contrario, verrà restituito l'errore #NUM!.
1,16 Calcolare i giorni lavorativi con o senza festività compresi tra due date
A volte potrebbe essere necessario calcolare il numero di giorni lavorativi compresi tra due date, includendo o escludendo le festività.
In questa sezione si utilizzerà la funzione GIORNI.LAVORATIVI.INTL:
Fai clic su GIORNI.LAVORATIVI.INTL per scoprirne argomenti e modalità d’uso.
Contare i giorni lavorativi includendo le festività
Per contare i giorni lavorativi includendo le festività tra due date nelle celle A2 e B2, utilizzare la seguente formula:
=NETWORKDAYS.INTL(A2,B2)
Premere il tasto Invio per ottenere il risultato.
Contare i giorni lavorativi escludendo le festività
Per contare i giorni lavorativi tra due date nelle celle A2 e B2, escludendo le festività comprese nell'intervallo D5:D9, utilizzare la seguente formula:
=GIORNI.LAVORATIVI.INTL(A5;B5;1;D5:D9)
Premere il tasto Invio per ottenere il risultato.
Nota:
Nelle formule sopra indicate, sabato e domenica sono considerati giorni festivi. Se la vostra definizione dei fine settimana è diversa, modificate l’argomento [weekend] di conseguenza.
1,17 Contare i fine settimana compresi tra due date
Se si desidera contare il numero di weekend compresi tra due date, le funzioni MATR.SOMMA.PRODOTTO o SOMMA possono rivelarsi utili.
Per contare i fine settimana (sabato e domenica) compresi tra due date nelle celle A12 e B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Oppure
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Premere il tasto Invio per ottenere il risultato.
1,18 Contare un giorno della settimana specifico compreso tra due date
Per contare il numero di occorrenze di un giorno specifico della settimana, ad esempio il lunedì, compreso tra due date, puoi combinare le funzioni INT e GIORNO.SETTIMANA.
Le celle A15 e B15 contengono le due date tra cui si desidera contare i lunedì; utilizzare la seguente formula:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Premere il tasto Invio per ottenere il risultato.
Modificare il numero del giorno della settimana nella funzione GIORNO.SETTIMANA per contare un giorno diverso:
1 è domenica, 2 è lunedì, 3 è martedì, 4 è mercoledì, 5 è giovedì, 6 è venerdì e 7 è sabato)
1,19 Calcolare il Giorni rimanenti nel mese/anno
A volte potrebbe essere utile conoscere il Giorni rimanenti nel mese o nell'anno in base alla data fornita, come mostrato nell'immagine seguente:
Ottenere il Giorni rimanenti nel mese corrente
Fai clic su EOMONTH per scoprire argomenti e utilizzi.
Per ottenere il Giorni rimanenti del mese corrente nella cella A2, utilizzare la seguente formula:
=EOMONTH(A2,0)-A2
Premere il tasto Invio e trascinare il quadratino di riempimento automatico per applicare questa formula ad altre celle, se necessario.
Suggerimento: i risultati potrebbero apparire in formato data; basta modificarne il formato in Generale o Numero.
Ottenere il Giorni rimanenti dell'anno corrente
Per ottenere il Giorni rimanenti dell'anno corrente nella cella A2, utilizzare la seguente formula:
=DATE(YEAR(A2),12,31)-A2
Premere il tasto Invio e trascinare il quadratino di riempimento automatico per applicare questa formula ad altre celle, se necessario.
1,21 Calcolare la differenza tra due orari
Per calcolare la differenza tra due orari, ecco due formule semplici che ti saranno d’aiuto.
Supponendo che le celle A2 e B2 contengano rispettivamente l’orario di inizio e quello di fine, utilizzare le seguenti formule:
=B2-A2
=TEXT(B2-A2,«hh:mm:ss»)
Premere il tasto Invio per ottenere il risultato.
Nota:
- Se utilizzi fine_orario - inizio_orario, puoi formattare il risultato nel formato orario desiderato tramite la finestra di dialogo Imposta formato cella.
- Se utilizzi TESTO(fine_orario-primo_orario;«formato_orario»), inserisci nella formula il formato orario in cui desideri visualizzare il risultato; ad esempio, TESTO(fine_orario-primo_orario;«h») restituisce 16.
- Se l’orario finale è precedente a quello iniziale, entrambe le formule restituiscono un errore. Per risolvere il problema, anteponi la funzione ASS a tali formule—ad esempio, ASS(B2-A2) o ASS(TESTO(B2-A2;«hh:mm:ss»))—e formatta quindi il risultato come orario.
1,22 Calcolare la differenza tra due orari in ore/minuti/secondi
Per calcolare la differenza tra due orari in ore, minuti o secondi, come illustrato nell’immagine seguente, segui questa procedura.
Ottenere la differenza in ore tra due orari
Per ottenere la differenza in ore tra due orari nelle celle A5 e B5, utilizzare la seguente formula:
=INT((B5-A5)*24)
Premere il tasto Invio e quindi formattare il risultato come Generale o Numero.
Per ottenere la differenza in ore decimali, utilizza (orario_fine - orario_inizio) × 24.
Ottenere la differenza in minuti tra due orari
Per ottenere la differenza in minuti tra due orari nelle celle A8 e B8, utilizzare la seguente formula:
=INT((B8-A8)*1440)
Premere il tasto Invio e quindi formattare il risultato come Generale o Numero.
Per ottenere la differenza in minuti decimali, utilizza (orario_fine - orario_inizio) × 1440.
Ottenere la differenza in secondi tra due orari
Per ottenere la differenza in secondi tra due orari nelle celle A5 e B5, utilizzare la seguente formula:
=(B11-A11)*86400)
Premere il tasto Invio e quindi formattare il risultato come Generale o Numero.
1,23 Calcola solo la differenza in ore tra due orari (non superiore a 24 ore)
Se la differenza tra due orari non supera le 24 ore, la funzione ORE ti permette di ottenere in modo rapido la differenza espressa in ore tra i due orari.
Fai clic su ORE per ulteriori dettagli su questa funzione.
Per ottenere la differenza in ore tra gli orari nelle celle A14 e B14, utilizzare la funzione ORE come segue:
=HOUR(B14-A14)
Premere il tasto Invio per ottenere il risultato.
L’orario di inizio deve essere precedente a quello di fine; in caso contrario, la formula restituisce l’errore #NUM!.
1,24 Calcola solo la differenza in minuti tra due orari (non superiore a 60 minuti)
La funzione MINUTO ti permette di ottenere in modo rapido solo la differenza in minuti tra questi due orari, ignorando ore e secondi.
Fai clic su MINUTO per ulteriori dettagli su questa funzione.
Per ottenere solo la differenza in minuti tra gli orari nelle celle A17 e B17, utilizzare la funzione MINUTO come segue:
=MINUTE(B17-A17)
Premere il tasto Invio per ottenere il risultato.
L’orario di inizio deve essere precedente all’orario di fine; in caso contrario, la formula restituisce l’errore #NUM!.
1,25 Calcola solo la differenza in secondi tra due orari (non superiore a 60 secondi)
La funzione SECONDO ti permette di ottenere in modo rapido solo la differenza in secondi tra questi due orari, ignorando ore e minuti.
Fai clic su SECONDO per ulteriori dettagli su questa funzione.
Per ottenere solo la differenza in secondi tra gli orari nelle celle A20 e B20, utilizzare la funzione SECONDO come segue:
=SECOND(B20-A20)
Premere il tasto Invio per ottenere il risultato.
L’orario di inizio deve essere precedente all’orario di fine; in caso contrario, la formula restituisce l’errore #NUM!.
1,26 Calcolare la differenza tra due orari e restituire ore, minuti e secondi
Per visualizzare la differenza tra due orari nel formato xx ore xx minuti xx secondi, utilizzare la funzione TESTO come mostrato di seguito:
Fai clic su TESTO per scoprire gli argomenti trattati e come utilizzare questa funzione.
Per calcolare la differenza tra gli orari nelle celle A23 e B23, utilizzare la seguente formula:
=TEXT(B23-A23,«h»« hours »«m»« minutes »«s»« seconds»«»).
Premere il tasto Invio per ottenere il risultato.
Nota:
Questa formula calcola esclusivamente la differenza in ore entro un intervallo di 24 ore e richiede che l’orario di fine sia successivo a quello di inizio; in caso contrario, restituisce l’errore #VALORE!.
1,27 Calcolare la differenza tra due valori datetime
Se sono presenti due valori datetime nel formato gg/mm/aaaa hh:mm:ss e si desidera calcolarne la differenza, è possibile utilizzare una delle seguenti formule in base alle proprie esigenze.
Ottenere la differenza tra due valori datetime e restituire il risultato nel formato hh:mm:ss
Prendendo come esempio due valori datetime nelle celle A2 e B2, utilizzare la seguente formula:
=B2-A2
Premere il tasto Invio per ottenere un risultato nel formato datetime, quindi formattare tale risultato come [h]:mm:ss nella categoria Personalizzato della scheda Numero della finestra di dialogo Imposta formato cella.
Ottenere la differenza tra due valori datetime e restituire giorni, ore, minuti e secondi
Prendendo come esempio due valori datetime nelle celle A5 e B5, utilizzare la seguente formula:
=INT(B5-A5) & « Days, » & HOUR(B5-A5) & « Hours, » & MINUTE(B5-A5) & « Minutes, » & SECOND(B5-A5) & « Seconds »
Premere il tasto Invio per ottenere il risultato.
Nota: in entrambe le formule, il datetime di fine deve essere successivo a quello di inizio; in caso contrario, le formule restituiscono un valore di errore.
1,28 Calcola la differenza temporale includendo i millisecondi
Innanzitutto, è necessario sapere come formattare la cella per visualizzare i millisecondi:
Seleziona le celle in cui desideri visualizzare i millisecondi, fai clic con il pulsante destro del mouse e scegli Imposta formato cella per aprire la finestra di dialogo Imposta formato cella. Nella scheda Numero, seleziona Personalizzato dall’elenco Categoria e digita hh:mm:ss.000 nella casella di testo.
Utilizzare la formula:
Per calcolare la differenza tra due orari nelle celle A8 e B8, utilizzare la seguente formula:
=ABS(B8-A8)
Premere il tasto Invio per ottenere il risultato.
1,29 Calcola le ore lavorative tra due date escludendo weekend
A volte potrebbe essere necessario calcolare le ore lavorative comprese tra due date, escludendo i weekend (sabato e domenica).
Considerando che le ore lavorative siano fissate a 8 ore al giorno, per calcolare le ore lavorative comprese tra due date indicate nelle celle A16 e B16, utilizzare la seguente formula:
=NETWORKDAYS(A16,B16) * 8
Premere il tasto Invio e quindi formattare il risultato come Generale o Numero.
Per ulteriori esempi sul calcolo delle ore lavorative comprese tra due date, consultare Calcolare le ore lavorative tra due date in Excel
Se hai installato Kutools per Excelin Excel, il 90 % dei calcoli relativi alla differenza tra valori datetime si risolve in un attimo, senza dover ricordare nessuna formula.
1,31 Calcolare la differenza tra due valori datetime con Assistente data e ora
Per calcolare la differenza tra due valori datetime in Excel, basta usare lo strumento Assistente Data e Ora.
1. Seleziona una cella in cui inserire il risultato calcolato e fai clic su Kutools > Assistente formula > Assistente Data e Ora.
2. Nella finestra di dialogo Assistente Data e Ora visualizzata, effettua le seguenti impostazioni:
- Seleziona l'opzione Differenza;
- Seleziona la data/ora iniziale e la data/ora finale nella sezione Inserimento argomento, oppure inserisci manualmente il valore datetime nella casella di input o fai clic sull'icona del calendario per selezionare la data;
- Seleziona Tipo di risultato di output da Elenco a discesa;
- Anteprima del risultato nella sezione Risultato.
3. Fare clic su OK. Il risultato calcolato viene visualizzato: trascina il quadratino di riempimento automatico sulle altre celle da elaborare.
Suggerimento:
Se si desidera ottenere la differenza tra due valori datetime e visualizzare il risultato in giorni, ore e minuti con Kutools per Excel, procedere come segue:
Seleziona una cella in cui desideri inserire il risultato e fai clic su Kutools > Assistente formula > Data e ora > Contare giorni, ore e minuti tra due date.
Nella finestra di dialogo Assistente formula, specifica la data di inizio e la data di fine, quindi fai clic su Ok.
Il risultato della differenza verrà visualizzato in giorni, ore e minuti.
Fai clic su Assistente Data e Ora per scoprire tutti gli utilizzi di questa funzionalità!
Fai clic su Kutools per Excel per scoprire tutte le funzionalità di questo componente aggiuntivo.
Fare clic su Download gratuitoper ottenere una prova gratuita di 30 giorni di Kutools per Excel
Vuoi contare rapidamente i weekend, i giorni lavorativi o un giorno specifico della settimana compresi tra due date? L’Assistente formula di Kutools per Excel ti dà una mano!
1. Seleziona la cella in cui desideri inserire il risultato calcolato, quindi fai clic su Kutools > Statistica > Numero di giorni non lavorativi tra due date / Numero di giorni lavorativi tra due date / Numero di giorni della settimana tra due date.
2. Nella finestra di dialogo Assistente formula che compare, specifica la data di inizio e la data di fine; se applichi Numero di giorni della settimana tra due date, devi anche indicare il giorno della settimana.
Per contare un giorno specifico della settimana, consulta la nota: usa i numeri da 1 a 7 per indicare i giorni da domenica a sabato.
3. Fare clic su Ok, quindi trascinare il quadratino di riempimento automatico sulle celle in cui si desidera contare il numero di weekend, giorni lavorativi o giorni specifici della settimana, se necessario.
Fare clic su Kutools per Excelper scoprire tutte le funzionalità di questo componente aggiuntivo.
Fare clic su Download gratuitoper ottenere una prova gratuita di 30 giorni di Kutools per Excel
2. Aggiungi o sottrai data e ora
Oltre al calcolo della differenza tra due date o orari, anche l’aggiunta e la sottrazione sono operazioni comuni per gestire il tempo in Excel. Ad esempio, potresti aver bisogno di determinare la data di scadenza partendo dalla data di produzione e dal numero di giorni di conservazione di un prodotto.
2,11 Aggiungere o sottrarre giorni a una data
Per aggiungere o sottrarre un numero specifico di giorni a una data, puoi scegliere tra due metodi diversi.
Supponendo di voler aggiungere 21 giorni a una data presente nella cella A2, scegliere uno dei metodi seguenti per risolvere il problema:
Metodo 1 data+giorni
Selezionare una cella e digitare la formula:
=A+21
Premere il tasto Invio per ottenere il risultato.
Per sottrarre 21 giorni, basta sostituire il segno più (+) con il segno meno (-).
Metodo 2 Incolla speciale
1. Digita il numero di giorni da aggiungere in una cella, ad esempio nella cella C2, quindi premi Ctrl+C per copiarlo.
2. Seleziona quindi le date a cui vuoi aggiungere 21 giorni, fai clic con il tasto destro per aprire il menu contestuale e scegli Incolla speciale....
3. Nella finestra di dialogo Incolla speciale, seleziona l'opzione Aggiungi(se desideri sottrarre giorni, scegli invece l'opzione)Sottrai). Clicca su OK.
4. Ora le date originali vengono visualizzate come numeri a cinque cifre; riformattale nuovamente come date.
2,12 Aggiungere o sottrarre mesi a una data
Per aggiungere o sottrarre mesi a una data, utilizza la funzione DATA.MESE (EDATE).
Fai clic su DATA.MESE (EDATE) per approfondire argomenti e utilizzi.
Supponendo di voler aggiungere 6 mesi alla data presente nella cella A2, utilizzare la seguente formula:
=EDATE(A2,6)
Premere il tasto Invio per ottenere il risultato.
Per sottrarre 6 mesi alla data, sostituire 6 con -6.
2,13 Aggiungere o sottrarre anni a una data
Per aggiungere o sottrarre n anni a una data, puoi usare una formula che combina le funzioni DATA, ANNO, MESE e GIORNO.
Supponendo di voler aggiungere 3 anni alla data presente nella cella A2, utilizzare la seguente formula:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Premere il tasto Invio per ottenere il risultato.
Se si desidera sottrarre 3 anni alla data, sostituire 3 con -3.
2,14 Aggiungere o sottrarre settimane a una data
Per aggiungere o sottrarre settimane a una data, la formula generale è la seguente:
Supponendo di voler aggiungere 4 settimane alla data presente nella cella A2, utilizzare la seguente formula:
=A2+4*7
Premere il tasto Invio per ottenere il risultato.
Per sottrarre 4 settimane alla data, sostituire il segno più (+) con il segno meno (-).
2,15 Aggiungere o sottrarre giorni lavorativi includendo o escludendo festività
Questa sezione spiega come usare la funzione GIORNO.LAVORATIVO (WORKDAY) per aggiungere o sottrarre giorni lavorativi a una data specificata, con la possibilità di escludere o includere le festività.
Visita GIORNO.LAVORATIVO (WORKDAY) per ulteriori dettagli sugli argomenti e sul suo utilizzo.
Aggiungere giorni lavorativi includendo le festività
Nella cella A2 è presente la data di riferimento e nella cella B2 il numero di giorni da aggiungere; utilizzare la seguente formula:
=WORKDAY(A2,B2)
Premere il tasto Invio per ottenere il risultato.
Aggiungere giorni lavorativi escludendo le festività
Nella cella A5 è presente la data di riferimento, nella cella B5 il numero di giorni da aggiungere e nell’intervallo D5:D8 sono elencate le festività; utilizzare la seguente formula:
=WORKDAY(A5,B5,D5:D8)
Premere il tasto Invio per ottenere il risultato.
Nota:
La funzione GIORNO.LAVORATIVO considera sabato e domenica come weekend; se i tuoi weekend cadono di sabato e domenica, puoi utilizzare la funzione GIORNO.LAVORATIVO.INTL (WORKDAY.INTL), che ti permette di specificare con precisione i giorni del weekend.
Visita GIORNO.LAVORATIVO.INTL (WORKDAY.INTL) per ulteriori informazioni.
Per sottrarre giorni lavorativi a una data, basta inserire un numero negativo di giorni nella formula.
2,16 Aggiungere o sottrarre anni, mesi e giorni specifici a una data
Per aggiungere un numero specifico di anni, mesi e giorni a una data, una formula che combina le funzioni DATA, ANNO, MESE e GIORNO può fare al caso vostro.
Per aggiungere 1 anno, 2 mesi e 30 giorni a una data presente in A11, utilizzare la seguente formula:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Premere il tasto Invio per ottenere il risultato.
Per sottrarre, sostituisci tutti i segni più (+) con segni meno (-).
2,21 Aggiungere o sottrarre ore/minuti/secondi a una data/ora
Di seguito trovi alcune formule per aggiungere o sottrarre ore, minuti o secondi a una data/ora.
Aggiungere o sottrarre ore a una data/ora
Supponendo di voler aggiungere 3 ore a una data/ora (o anche solo a un orario) presente nella cella A2, utilizzare la seguente formula:
=A2+3/24
Premere il tasto Invio per ottenere il risultato.
Aggiungere o sottrarre minuti a una data/ora
Supponendo di voler aggiungere 15 minuti a una data/ora (o anche solo a un orario) presente nella cella A5, utilizzare la seguente formula:
=A2+15/1440
Premere il tasto Invio per ottenere il risultato.
Aggiungere o sottrarre secondi a una data/ora
Supponendo di voler aggiungere 20 secondi a una data/ora (o anche solo a un orario) presente nella cella A8, utilizzare la seguente formula:
=A2+20/86400
Premere il tasto Invio per ottenere il risultato.
2,22 Somma orari superiori a 24 ore
Supponiamo che tu abbia una tabella di Excel che registra l’orario di lavoro di tutto il personale in una settimana. Per sommare il tempo lavorativo totale ai fini del calcolo delle retribuzioni, puoi utilizzare SOMMA(intervallo) per ottenere il risultato. Tuttavia, di solito la somma viene visualizzata come un orario non superiore a 24 ore, come mostrato nello screenshot seguente. Come fare per ottenere il risultato corretto?
In realtà, basta formattare il risultato come [hh]:mm:ss.
Fare clic con il tasto destro sulla cella del risultato e scegliere Imposta formato cella nel menu contestuale. Nella finestra di dialogo Imposta formato cella visualizzata, selezionare Personalizzato dall’elenco e digitare [hh]:mm:ss nella casella di testo a destra, quindi fare clic su OK.
Il risultato della somma verrà visualizzato in modo corretto.
2,23 Aggiungi ore lavorative a una data escludendo weekend e festività
Di seguito è riportata una formula avanzata per calcolare la Data di fine aggiungendo un numero specifico di ore lavorative alla Data di inizio, escludendo weekend (sabato e domenica) e giorni festivi.
In una tabella di Excel, la cella A11 contiene la data e l’ora di inizio, mentre la cella B11 riporta le ore lavorative. Negli intervalli E11 ed E13 sono indicati rispettivamente gli orari di inizio e fine del lavoro, e la cella E15 specifica il giorno festivo da escludere.
Utilizzare la seguente formula:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Premere il tasto Invio per ottenere il risultato.
Se hai Kutools per Excel installato, un solo strumento – Assistente Data e Ora – è in grado di gestire la maggior parte dei calcoli per aggiungere e sottrarre date e orari.
1. Fai clic su una cella in cui desideri visualizzare il risultato, quindi applica questo strumento selezionando Kutools > Assistente formula > Assistente Data e Ora.
2. Nella finestra di dialogo Assistente Data e Ora, seleziona l’opzione Aggiungi o l’opzione Sottrai in base alle tue esigenze. Scegli quindi la cella oppure digita direttamente la data/ora da utilizzare nella sezione Inserimento argomento, specifica gli anni, mesi, settimane, giorni, ore, minuti e secondi da aggiungere o sottrarre, quindi fai clic su Ok. Vedere lo screenshot:
Puoi visualizzare in anteprima il risultato calcolato nella sezione Risultato.
Ora il risultato è stato inserito: trascina il quadratino di riempimento automatico sulle altre celle per ottenere i risultati.
Fare clic su Assistente Data e Oraper scoprire altri modi in cui puoi sfruttare questa funzionalità.
Fare clic su Kutools per Excelper conoscere tutte le funzionalità di questo componente aggiuntivo.
Fare clic su Download gratuitoper ottenere una versione di prova gratuita di 30 giorni di Kutools per Excel
2,41 Verificare o evidenziare se una data è scaduta
Se è presente un elenco di Data di scadenza di prodotti, potrebbe essere utile verificare ed evidenziare le date scadute in base alla data odierna, come illustrato nello screenshot seguente.
In realtà, la funzionalità Utilizza la formattazione condizionale può gestire rapidamente questa operazione.
1. Seleziona le date da verificare, quindi fai clic su Home > Utilizza la formattazione condizionale > Nuova regola.
2. Nella finestra di dialogo Nuova regola di formattazione, seleziona Usa una formula per determinare le celle da formattare nella sezione Seleziona un tipo di regola, quindi digita =B2<OGGI() nella casella di input (B2 è la prima data da verificare) e fai clic su Formato per aprire la finestra di dialogo Imposta formato cella. Scegli poi una formattazione personalizzata per evidenziare le date di scadenza in base alle tue esigenze. Fai clic su OK > OK.
2,42 Restituire la fine del mese corrente / il primo giorno del Mese prossimo
La data di scadenza di alcuni prodotti cade nell’ultimo giorno del mese di produzione o nel primo giorno del mese successivo. Per generare rapidamente l’elenco delle date di scadenza in base alla data di produzione, segui questa procedura.
Ottenere la fine del mese corrente
Nella cella B13 è presente una data di produzione. Utilizzare la seguente formula:
=EOMONTH(B13,0)
Premere il tasto Invio per ottenere il risultato.
Ottenere il 1° giorno del Mese prossimo
Nella cella B18 è presente una data di produzione. Utilizzare la seguente formula:
=EOMONTH(B18,0)+1
Premere il tasto Invio per ottenere il risultato.
3. Calcola l’età
Questa sezione illustra i metodi per calcolare l’età in base a una data specifica o a un numero seriale.
3,11 Calcolare l’età in base alla data di nascita fornita
Ottenere l’età come numero decimale in base alla data di nascita
Fai clic su FRAZIONE.ANNO per scoprire tutti i dettagli sugli argomenti e sull’utilizzo.
Ad esempio, per calcolare le età in base all’elenco di date di nascita nella colonna B2:B9, utilizzare la seguente formula:
=YEARFRAC(B2,TODAY())
Premi il tasto Invio, poi trascina il quadratino di riempimento automatico verso il basso finché non vengono calcolate tutte le età.
Suggerimento:
1) È possibile specificare il numero di decimali desiderato nella finestra di dialogo Imposta formato cella.
2) Per calcolare l’età in una data specifica in base a una data di nascita fornita, sostituire OGGI() con la data specifica racchiusa tra virgolette doppie, ad esempio =YEARFRAC(B2,«[[PH_324]]»)
3) Per calcolare l’età che si avrà il prossimo anno in base alla data di nascita, basta aggiungere 1 alla formula, ad esempio: =FRAZIONE.ANNO(B2;OGGI())+1.
Ottenere l’età come numero intero in base alla data di nascita
Fai clic su DATA.DIFF per informazioni dettagliate sugli argomenti e sull’utilizzo.
Utilizzando l’esempio precedente, per calcolare l’età in base alle date di nascita presenti nell’elenco B2:B9, utilizzare la seguente formula:
=DATEDIF(B2,TODAY(),«y»)
Premi Invio, poi trascina il quadratino di riempimento automatico verso il basso finché non vengono calcolate tutte le età.
Suggerimento:
1) Per calcolare l’età in una data specifica a partire dalla data di nascita fornita, sostituisci OGGI() con la data desiderata racchiusa tra virgolette doppie, ad esempio =DATEDIF(B2,«[[PH_329]]»,«y»).
2) Per calcolare l’età che si avrà il prossimo anno in base alla data di nascita, basta aggiungere 1 alla formula, ad esempio: =DATEDIF(B2;OGGI();«y»)+1.
3,12 Calcolare l’età in anni, mesi e giorni in base alla data di nascita fornita
Per calcolare l’età in base a una data di nascita fornita e visualizzare il risultato nel formato «xx anni, xx mesi, xx giorni», come illustrato nello screenshot seguente, è disponibile una formula complessa che può essere utile.
Per ottenere l’età in anni, mesi e giorni in base alla data di nascita presente nella cella B12, utilizzare la seguente formula:
=DATEDIF(B12,TODAY(),«Y»)&« Years, »&DATEDIF(B12,TODAY(),«YM»)&« Months, »&DATEDIF(B12,TODAY(),«MD»)&« Days»
Premere il tasto Invio per ottenere l’età, quindi trascinare il quadratino di riempimento automatico sulle altre celle.
Suggerimento:
Per calcolare l’età in una data specifica a partire da una data di nascita fornita, sostituisci OGGI() con la data desiderata racchiusa tra virgolette doppie, ad esempio: =DATEDIF(B12,«[[PH_337]]»,«Y»)&« Anni, »&DATA.DIFF(B12,«1/1/2021»,«YM»)&« Mesi, »&DATA.DIFF(B12,«1/1/2021»,«MD»)&« Giorni».
3,13 Calcolare l’età in base alla data di nascita antecedente al 1/1/1900
In Excel, le date antecedenti al 1/1/1900 non possono essere inserite come valori data/ora né calcolate correttamente. Tuttavia, per calcolare l’età di una persona famosa in base alla data di nascita (anteriore al 1/11900) e alla data di decesso, è necessario ricorrere a un codice VBA.
1. Premere Alt+F11 per aprire la finestra di Microsoft Visual Basic for Applications, quindi fare clic sulla scheda Inserisci e scegliere Modulo per creare un nuovo modulo.
2. Copia quindi il codice seguente nel nuovo modulo.
VBA: Calcolare l'età prima di 1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Salvare il codice, tornare al foglio, selezionare una cella in cui inserire l'età calcolata e digitare =AgeFunc(datadinascita,datadecesso). In questo caso, =AgeFunc(B22,C22). Premere Invio per ottenere l'età e, se necessario, utilizzare il quadratino di riempimento automatico per applicare la formula ad altre celle.
Se hai installato Kutools per Excel in Excel, puoi utilizzare lo strumento Assistente Data e Ora per calcolare l'età in modo semplice e veloce!
1. Seleziona una cella in cui desideri inserire l'età calcolata e fai clic su Kutools > Assistente formula > Assistente Data e Ora.
2. Nella finestra di dialogo Assistente Data e Ora,
- 1) Seleziona l'opzione Età;
- 2) Scegliere la cella della data di nascita oppure inserire direttamente la data di nascita o fare clic sull'icona del calendario per selezionarla;
- 3) Selezionare l'opzione Oggise si desidera calcolare l'età attuale; selezionare l'opzione Data specificae inserire la data se si desidera calcolare l'età in un momento passato o futuro;
- 4) Specificare il tipo di output dal Elenco a discesa;
- 5) Anteprima del risultato. Fai clic su OK.
Fai clic su Assistente Data e Ora per scoprire come utilizzare questa funzionalità.
Fai clic su Kutools per Excel per visualizzare tutte le funzionalità di questo componente aggiuntivo.
Fare clic su Download gratuitoper ottenere una prova gratuita di 30 giorni di Kutools per Excel
3,31 Ottenere la data di nascita dal numero di identificazione
Se in un elenco di numeri di identificazione le prime 6 cifre rappresentano la data di nascita (ad esempio, 920315330 corrisponde al 15/03/1992), come si può estrarre rapidamente tale data in un’altra colonna?
Prendiamo ora come esempio l'elenco di numeri di identificazione che inizia nella cella C2 e utilizziamo la seguente formula:
=MID(C2,5,2)&«/»&MID(C2,3,2)&«/»&MID(C2,1,2)
Premi il tasto Invio, quindi trascina il quadratino di riempimento verso il basso per ottenere gli altri risultati.
Nota:
È possibile personalizzare i riferimenti nella formula in base alle proprie esigenze. Ad esempio, se il numero di identificazione è 13219920420392 e la data di nascita corrispondente è 04/20/1992, basta modificare la formula in =STRINGA.ESTRAI(C2;8;2)&«/»&STRINGA.ESTRAI(C2;10;2)&«/»&STRINGA.ESTRAI(C2;4;4) per ottenere il risultato desiderato.
3,32 Calcolare l'età dal numero di identificazione
Se in un elenco di numeri di identificazione le prime 6 cifre rappresentano la data di nascita (ad esempio, 920315330 corrisponde al 15/03/1992), come si può calcolare rapidamente l’età in Excel a partire da ciascun numero?
Prendiamo ora come esempio l'elenco di numeri di identificazione che inizia nella cella C2 e utilizziamo la seguente formula:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),«YY»),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),«y»)
Premi il tasto Invio, quindi trascina il quadratino di riempimento verso il basso per ottenere gli altri risultati.
Nota:
In questa formula, se l’anno è inferiore all’anno corrente, verrà interpretato come se iniziasse con “20”: ad esempio, 200203943 corrisponderà all’anno 2020. Se invece l’anno è superiore a quello corrente, verrà considerato come se iniziasse con “19”: ad esempio, 920420392 sarà interpretato come l’anno 1992.
Altri tutorial su Excel:
Combinare più cartelle di lavoro o fogli di lavoro in un unico file
Questo tutorial copre quasi tutti gli scenari di combinazione che potresti incontrare e ti offre soluzioni professionali mirate.
Dividere celle di testo, numeriche e di data (separare in più colonne)
Questo tutorial è suddiviso in tre parti: divisione di celle di testo, di celle numeriche e di celle di data. Ogni sezione include diversi esempi pratici per aiutarti a gestire al meglio la suddivisione quando ti trovi ad affrontare lo stesso problema.
Combinare i contenuti di più celle senza perdere dati in Excel
Questo tutorial si concentra sull'estrazione da una posizione specifica all'interno di una cella e presenta diversi metodi per aiutarti a estrarre testo o numeri da una cella in base a una posizione specifica in Excel.
Confrontare due colonne per trovare corrispondenze e differenze in Excel
Questo articolo copre la maggior parte degli scenari che potresti incontrare nel confronto tra due colonne e si augura di esserti davvero utile.
I migliori strumenti per la produttività in ufficio
Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività del 80 %
- Super Barra delle formule (modifica facilmente più righe di testo e formule); Layout di lettura (leggi e modifica con facilità un gran numero di celle); Incolla in Intervallo di filtro...
- Unisci celle/righe/colonne e conserva i dati; dividi il contenuto delle celle; combina righe duplicate e calcola somma/media…; previeni voci duplicate; confronta intervalli…
- Seleziona duplicati o valori univoci nelle righe; Seleziona righe vuote (tutte le celle sono vuote); Super Trova e ricerca fuzzy in molte cartelle di lavoro; selezione casuale…
- Copia esatta di celle multiple senza modificare i riferimenti delle formule; creazione automatica di riferimenti a più fogli; inserisci punti elenco, caselle di controllo e molto altro...
- Formule preferite e inserimento rapido, intervalli, grafici e immagini; crittografa le celle con password; crea un elenco di invio e invia email...
- Estrai testo, aggiungi testo, rimuovi caratteri da una posizione specifica, elimina spazi; crea e stampa statistiche dei dati della pagina; converti il contenuto delle celle in commenti e viceversa...
- Super Filtri (salva e applica schemi di filtro ad altri fogli); Ordinamento avanzato per mese, settimana, giorno, frequenza e molto altro; Filtro speciale per grassetto, corsivo…
- Combina cartelle e fogli di lavoro; unisci tabelle in base a una colonna chiave; suddividi i dati in più fogli; converti in batch file xls, xlsx e PDF...
- Raggruppa la tabella pivot per numero di settimana, giorno della settimana e altro ancora...Mostra le celle sbloccate e blocca la selezione con colori diversi;Evidenzia le celle contenenti formule o nomi...
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e riduci centinaia di clic del mouse ogni giorno!
