Tutorial Excel: calcolo DateTime (calcola differenza, età, aggiungi/sottrai)
In Excel, il calcolo della data e dell'ora viene utilizzato frequentemente, ad esempio calcolare la differenza tra due date/ora, aggiungere o sottrarre datetime, ottenere l'età in base alla data di nascita e così via. Qui in questo tutorial, elenca quasi scenari sul calcolo della data e ora e fornisce i metodi correlati per te.
In questo tutorial, creo alcuni esempi per spiegare i metodi, puoi modificare i riferimenti di cui hai bisogno quando usi il codice o le formule VBA di seguito
1. Calcola la differenza tra due date/ore
Calcolare la differenza tra due date o due volte può essere il problema più normale del calcolo della data e dell'ora che incontri nel lavoro quotidiano di Excel. I seguenti esempi forniti possono aiutarti a migliorare l'efficienza quando incontri gli stessi problemi.
1.11 Calcolare la differenza tra due date in giorni/mesi/anni/settimane
La funzione DATEDIF di Excel può essere utilizzata per calcolare rapidamente la differenza tra due date in giorni, mesi, anni e settimane.
Fare clic per maggiori dettagli su DATADIF function
Giorni di differenza tra due date
Per ottenere la differenza di giorni tra due date nella cella A2 e B2, utilizzare la formula come questa
=DATEDIF(A2,B2,"d")
STAMPA Entra chiave per ottenere il risultato.
Mesi di differenza tra due date
Per ottenere la differenza di mesi tra due date nella cella A5 e B5, utilizzare la formula come questa
=DATEDIF(A5,B5,"m")
STAMPA Entra chiave per ottenere il risultato.
Anni di differenza tra due date
Per ottenere la differenza di anni tra due date nella cella A8 e B8, utilizzare la formula come questa
=DATEDIF(A8,B8,"y")
STAMPA Entra chiave per ottenere il risultato.
Differenza di settimane tra due date
Per ottenere la differenza di settimane tra due date nella cella A11 e B11, utilizzare la formula come questa
=DATEDIF(A11,B11,"d")/7
STAMPA Entra chiave per ottenere il risultato.
NOTA:
1) Quando si utilizza la formula sopra per ottenere la differenza di settimane, potrebbe restituire un risultato in formato data, è necessario formattare il risultato in generale o numero in base alle proprie esigenze.
2) Quando si utilizza la formula sopra per ottenere la differenza delle settimane, potrebbe tornare a un numero decimale, se si desidera ottenere il numero intero della settimana, è possibile aggiungere la funzione ROUNDDOWN prima come mostrato di seguito per ottenere la differenza delle settimane intere:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calcola mesi ignora anni e giorni tra due date
Se vuoi solo calcolare la differenza di mesi ignorando anni e giorni tra due date come mostra lo screenshot qui sotto, ecco una formula che può aiutarti.
=DATEDIF(A2,B2,"ym")
STAMPA Entra chiave per ottenere il risultato.
A2 è la data di inizio e B2 è la data di fine.
1.13 Calcola giorni ignora anni e mesi tra due date
Se vuoi solo calcolare la differenza di giorni ignorando anni e mesi tra due date come mostrato nell'immagine sottostante, ecco una formula che può aiutarti.
=DATEDIF(A5,B5,"md")
STAMPA Entra chiave 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 vuoi ottenere la differenza tra due date e restituire xx anni, xx mesi e xx giorni come mostra lo screenshot qui sotto, ecco anche una formula fornita.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
STAMPA Entra chiave per ottenere il risultato.
A8 è la data di inizio e B8 è la data di fine.
1.15 Calcola la differenza tra una data e oggi
Per calcolare automaticamente la differenza tra una data e oggi, cambia semplicemente end_date nelle formule sopra in OGGI(). Qui prendi per calcolare la differenza di giorni tra una data passata e oggi come esempio.
=DATEDIF(A11,TODAY(),"d")
STAMPA Entra chiave per ottenere il risultato.
Note:: se vuoi calcolare la differenza tra una data futura e oggi, cambia start_date in oggi e prendi la data futura come end_date in questo modo:
=DATEDIF(TODAY(),A14,"d")
Si noti che la data_iniziale deve essere minore di data_fine nella funzione DATEDIF, altrimenti tornerà a #NUM! valore di errore.
1.16 Calcola i giorni lavorativi con o senza ferie tra due date
A volte, potrebbe essere necessario contare il numero di giorni lavorativi con o senza ferie tra due date date.
In questa parte utilizzerai la funzione NETWORKDAYS.INTL:
Clicchi GIORNI.LAVORATIVI.INTLINT conoscerne gli argomenti e l'uso.
Conta i giorni lavorativi con i giorni festivi
Per contare i giorni lavorativi con giorni festivi compresi tra due date nella cella A2 e B2, utilizzare la formula come questa:
=NETWORKDAYS.INTL(A2,B2)
STAMPA Entra chiave per ottenere il risultato.
Conta i giorni lavorativi senza ferie
Per contare i giorni lavorativi con giorni festivi compresi tra due date nella cella A2 e B2 ed escludendo i giorni festivi nell'intervallo D5:D9, utilizzare la formula come questa:
=GIORNI.RETE.INTL(A5,B5,1,D5:D9)
STAMPA Entra chiave per ottenere il risultato.
NOTA:
Nelle formule di cui sopra, prendono il sabato e la domenica come fine settimana, se hai giorni di fine settimana diversi, cambia l'argomento [fine settimana] di cui hai bisogno.
1.17 Calcola i fine settimana tra due date
Se vuoi contare il numero di fine settimana tra due date, le funzioni SUMPRODUCT o SUM possono farti un favore.
Per contare i fine settimana (sabato e domenica) tra due date nella cella A12 e B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
STAMPA Entra chiave per ottenere il risultato.
1.18 Calcola il giorno della settimana specifico tra due date
Per contare il numero di un giorno della settimana specifico come il lunedì tra due date, la combinazione delle funzioni INT e GIORNO DELLA SETTIMANA può aiutarti.
La cella A15 e B15 sono le due date che vuoi contare lunedì, per favore usa la formula come questa:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
STAMPA Entra chiave per ottenere il risultato.
Modificare il numero del giorno della settimana nella funzione GIORNO SETTIMANA per contare un giorno della settimana diverso:
1 è domenica, 2 è lunedì, 3 è martedì, 4 è mercoledì, 5 è giovedì, 6 è venerdì e 7 è sabato)
1.19 Calcola i giorni rimanenti in mese/anno
A volte, potresti voler conoscere i giorni rimanenti nel mese o nell'anno in base alla data fornita, come mostra lo screenshot qui sotto:
Ottieni i giorni rimanenti nel mese corrente
Clicchi EOMONTH per conoscere l'argomento e l'uso.
Per ottenere i giorni rimanenti del mese corrente nella cella A2, utilizzare la formula come questa:
=EOMONTH(A2,0)-A2
STAMPA Entra chiave e trascinare la maniglia di riempimento automatico per applicare questa formula ad altre celle, se necessario.
Mancia: i risultati possono essere visualizzati come formato data, basta cambiarli come formato generale o numerico.
Ottieni i giorni rimanenti nell'anno in corso
Per ottenere i giorni rimanenti dell'anno in corso nella cella A2, utilizzare la formula come questa:
=DATE(YEAR(A2),12,31)-A2
STAMPA Entra chiave e trascinare la maniglia di riempimento automatico per applicare questa formula ad altre celle, se necessario.
1.21 Calcola la differenza tra due tempi
Per ottenere la differenza tra due tempi, ecco due semplici formule che possono aiutarti.
Supponendo che nella cella A2 e B2 contengano start_time e end_time separatamente, usando le formule come queste:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
STAMPA Entra chiave per ottenere il risultato.
NOTA:
- Se usi end_time-start_time, puoi formattare il risultato in un altro formato ora come necessario nella finestra di dialogo Formato celle.
- Se utilizzi TEXT(end_time-first_time,"time_format"), inserisci il formato dell'ora in cui desideri visualizzare il risultato nella formula, ad esempio TEXT(end_time-first_time,"h") restituisce 16.
- Se end_time è minore di start_time, entrambe le formule restituiscono valori di errore. Per risolvere questo problema, puoi aggiungere ABS all'inizio di queste formule, come ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), quindi formattare il risultato come ora.
1.22 Calcola la differenza tra due tempi in ore/minuti/secondi
Se vuoi calcolare la differenza tra due volte in ore, minuti o secondi come mostra lo screenshot qui sotto, segui questa parte.
Ottieni la differenza di ore tra due volte
Per ottenere la differenza di ore tra due tempi in A5 e B5, utilizzare la formula come questa:
=INT((B5-A5)*24)
STAMPA Entra , quindi formattare il risultato del formato dell'ora come generale o numero.
Se vuoi ottenere la differenza di ore decimali, usa (end_time-start_time)*24.
Ottieni minuti di differenza tra due volte
Per ottenere la differenza di minuti tra due tempi in A8 e B8, utilizzare la formula come questa:
=INT((B8-A8)*1440)
STAMPA Entra , quindi formattare il risultato del formato dell'ora come generale o numero.
Se vuoi ottenere la differenza dei minuti decimali, usa (end_time-start_time)*1440.
Ottieni secondi di differenza tra due volte
Per ottenere la differenza di secondi tra due volte in A5 e B5, utilizzare la formula come questa:
=(B11-A11)*86400)
STAMPA Entra , quindi formattare il risultato del formato dell'ora come generale o numero.
1.23 Calcola la differenza di ore solo tra due tempi (non superare le 24 ore)
Se la differenza tra due orari non supera le 24 ore, la funzione ORA può ottenere rapidamente la differenza di ore tra questi due orari.
Clicchi ORA per maggiori dettagli su questa funzione.
Per ottenere la differenza di ore tra gli orari nella cella A14 e B14, utilizzare la funzione ORA come segue:
=HOUR(B14-A14)
STAMPA Entra chiave per ottenere il risultato.
L'ora_di inizio deve essere inferiore all'ora_di fine, altrimenti la formula restituisce #NUM! valore di errore.
1.24 Calcolare i minuti di differenza solo tra due tempi (non superare i 60 minuti)
La funzione MINUTI può ottenere rapidamente gli unici minuti di differenza tra questi due tempi e ignorare ore e secondi.
Clicchi MINUTO per maggiori dettagli su questa funzione.
Per ottenere solo la differenza di minuti tra i tempi nella cella A17 e B17, utilizzare la funzione MINUTI come segue:
=MINUTE(B17-A17)
STAMPA Entra chiave per ottenere il risultato.
L'ora_di inizio deve essere inferiore all'ora_di fine, altrimenti la formula restituisce #NUM! valore di errore.
1.25 Calcola la differenza di secondi solo tra due tempi (non superare i 60 secondi)
La funzione SECONDA può ottenere rapidamente gli unici secondi di differenza tra questi due tempi e ignorare ore e minuti.
Clicchi SECONDA per maggiori dettagli su questa funzione.
Per ottenere solo la differenza di secondi tra i tempi nella cella A20 e B20, utilizzare la funzione SECONDA come questa:
=SECOND(B20-A20)
STAMPA Entra chiave per ottenere il risultato.
L'ora_di inizio deve essere inferiore all'ora_di fine, altrimenti la formula restituisce #NUM! valore di errore.
1.26 Calcolare la differenza tra due tempi e restituire ore, minuti, secondi
Se si desidera mostrare la differenza tra due volte come xx ore xx minuti xx secondi, utilizzare la funzione TESTO come mostrato di seguito:
Clicchi TESTO per realizzare gli argomenti e l'uso di questa funzione.
Per calcolare la differenza tra i tempi nella cella A23 e B23, utilizzare la formula in questo modo:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
STAMPA Entra chiave per ottenere il risultato.
NOTA:
Questa formula calcola anche solo la differenza di ore non superiore a 24 ore e l'ora di fine deve essere maggiore di ora di inizio, altrimenti restituisce #VALORE! valore di errore.
1.27 Calcola la differenza tra due datetime
Se ci sono due volte nel formato mm/gg/aaaa hh:mm:ss, per calcolare la differenza tra di loro, puoi utilizzare una delle seguenti formule di cui hai bisogno.
Ottieni la differenza di orario tra due datetime e restituisce il risultato nel formato hh:mm:ss
Prendi due datetime nella cella A2 e B2 come esempio, usa la formula come questa:
=B2-A2
STAMPA Entra chiave, restituendo un risultato in formato datetime, quindi formatta questo risultato come [H]: mm: ss nella categoria personalizzata sotto il Numero scheda in formato celle dialogo.
Ottieni la differenza tra due datetime e restituisci giorni, ore, minuti, secondi
Prendi due datetime nella cella A5 e B5 come esempio, usa la formula come questa:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
STAMPA Entra chiave per ottenere il risultato.
Nota: in entrambe le formule, end_datetime deve essere maggiore di start_datetime, altrimenti le formule restituiscono valori di errore.
1.28 Calcola la differenza di tempo con i millisecondi
Innanzitutto, devi sapere come formattare la cella per mostrare i millisecondi:
Seleziona le celle che vuoi mostrare in millisecondi e a destra per selezionare formato celle per abilitare il formato celle finestra di dialogo, selezionare Custom nel Categoria elenca nella scheda Numero e digita questo hh: mm: ss.000 nella casella di testo.
Usa la formula:
Qui per calcolare la differenza tra due volte nella cella A8 e B8, usa la formula come:
=ABS(B8-A8)
STAMPA Entra chiave per ottenere il risultato.
1.29 Calcolare l'orario di lavoro tra due date esclusi i fine settimana
A volte, potrebbe essere necessario contare l'orario di lavoro tra due date, esclusi i fine settimana (sabato e domenica).
Qui l'orario di lavoro è fissato a 8 ore al giorno e per calcolare l'orario di lavoro tra due date fornite nella cella A16 e B16, utilizzare la formula come questa:
=NETWORKDAYS(A16,B16) * 8
STAMPA Entra chiave e quindi formattare il risultato come generale o numero.
Per ulteriori esempi sul calcolo dell'orario di lavoro tra due date, visitare Ottieni ore di lavoro tra due date in Excel
Se si dispone di Kutools for Excel installato in Excel, il 90 percento dei calcoli della differenza data/ora può essere risolto rapidamente senza ricordare alcuna formula.
1.31 Calcola la differenza tra due datetime da Data & Time Helper
Per calcolare la differenza tra due datetime in Excel, basta il Data e ora Helper è abbastanza.
1. Selezionare una cella in cui inserire il risultato calcolato e fare clic Kutools > formula Helper > Data e ora helper.
2. Nel popping Data e ora Helper finestra di dialogo, seguire le impostazioni seguenti:
- Vedi Synhydrid opzione;
- Seleziona la data/ora di inizio e la data/ora di fine in Input di argomenti sezione, puoi anche inserire direttamente datetime manualmente nella casella di input o fare clic sull'icona del calendario per selezionare la data;
- Selezionare il tipo di risultato di output dall'elenco a discesa;
- Visualizza in anteprima il risultato in Risultato .
3. Clic Ok. Il risultato calcolato viene emesso e trascina il quadratino di riempimento automatico sulle celle che devi anche calcolare.
Mancia:
Se vuoi ottenere la differenza tra due datetime e mostrare il risultato come giorni, ore e minuti con Kutools for Excel, si prega di fare come di seguito:
Seleziona una cella in cui desideri posizionare il risultato e fai clic Kutools > formula Helper > Appuntamento > Conta giorni, ore e minuti tra due date.
Quindi nel Formule Helper finestra di dialogo, specificare la data di inizio e la data di fine, quindi fare clic su Ok.
E il risultato della differenza verrà mostrato come giorni, ore e minuti.
Clicchi Data e ora Helper per saperne di più sull'utilizzo di questa funzione.
Clicchi Kutools for Excel per conoscere tutte le funzionalità di questo componente aggiuntivo.
Clicchi Scarica gratis ottenere 30-giorni di prova gratuita di Kutools for Excel
Se vuoi contare rapidamente il fine settimana, i giorni lavorativi o un giorno della settimana specifico tra due date e orari, Kutools for Excel'S formula Helper il gruppo può aiutarti
1. Selezionare la cella che posizionerà il risultato calcolato, fare clic Kutools > Statistico > Numero di giorni non lavorativi tra due date/Numero di giorni lavorativi tra due date/Conta il numero di giorni specifici della settimana.
2. Nel saltar fuori Formule Helper finestra di dialogo, specificare la data di inizio e la data di fine, se si applica Conta il numero di un giorno specifico della settimana, è necessario specificare anche il giorno della settimana.
Per contare il giorno della settimana specifico, puoi fare riferimento alla nota per utilizzare 1-7 per indicare domenica-sabato.
3. Clic Oke quindi trascinare il quadratino di riempimento automatico sulle celle che devono contare il numero di fine settimana/giorno lavorativo/giorno della settimana specifico, se necessario.
Clicchi Kutools for Excel per conoscere tutte le funzionalità di questo componente aggiuntivo.
Clicchi Scarica gratis ottenere 30-giorni di prova gratuita di Kutools for Excel
2. Aggiungi o sottrai data e ora
Fatta eccezione per il calcolo della differenza tra due datetime, l'aggiunta o la sottrazione è anche il normale calcolo datetime in Excel. Ad esempio, potresti voler ottenere la data di scadenza in base alla data di produzione e al 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, ecco due metodi diversi.
Supponendo di aggiungere 21 giorni a una data nella cella A2, scegli uno dei metodi seguenti per risolverlo,
Metodo 1 data+giorni
Seleziona una cella e digita la formula:
=A+21
STAMPA Entra chiave per ottenere il risultato.
Se vuoi sottrarre 21 giorni, cambia il segno più (+) in segno meno (-).
Metodo 2 Incolla speciale
1. Digitare il numero di giorni che si desidera aggiungere in una cella supponendo nella cella C2, quindi premere Ctrl + C per copiarlo.
2. Quindi seleziona le date che desideri aggiungere 21 giorni, fai clic con il pulsante destro del mouse per visualizzare il menu di scelta rapida e seleziona Incolla speciale....
3. Nel Incolla speciale dialogo, controlla Aggiungi opzione (se vuoi sottrarre giorni, controlla Sottrarre opzione). Clic OK.
4. Ora le date originali cambiano in numeri a 5 cifre, formattale come date.
2.12 Aggiungere o sottrarre mesi a una data
Per aggiungere o sottrarre mesi a una data, è possibile utilizzare la funzione EDATE.
Clicchi EDATE per studiarne gli argomenti e l'uso.
Supponendo di aggiungere 6 mesi alla data nella cella A2, utilizzare la formula come questa:
=EDATE(A2,6)
STAMPA Entra chiave per ottenere il risultato.
Se vuoi sottrarre 6 mesi alla data, cambia da 6 a -6.
2.13 Aggiungere o sottrarre anni a una data
Per aggiungere o sottrarre n anni a una data, è possibile utilizzare una formula che combina le funzioni DATA, ANNO, MESE e GIORNO.
Supponendo di aggiungere 3 anni alla data nella cella A2, utilizzare la formula come questa:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
STAMPA Entra chiave per ottenere il risultato.
Se vuoi sottrarre 3 anni alla data, cambia 3 in -3.
2.14 Aggiungere o sottrarre settimane a una data
Per aggiungere o sottrarre settimane a una data, la formula generale è
Supponendo di aggiungere 4 settimane alla data nella cella A2, utilizzare la formula in questo modo:
=A2+4*7
STAMPA Entra chiave per ottenere il risultato.
Se vuoi sottrarre 4 settimane alla data, cambia il segno più (+) con il segno meno (-).
2.15 Aggiungere o sottrarre giorni lavorativi inclusi o esclusi i giorni festivi
In questa sezione viene illustrato come utilizzare la funzione GIORNO LAVORATIVO per aggiungere o sottrarre giorni lavorativi a una data data escluse le festività o comprese le festività.
Visita GIORNO.LAVORATIVO per conoscere maggiori dettagli sui suoi argomenti e utilizzo.
Aggiungi giorni lavorativi compresi i giorni festivi
Nella cella A2 c'è la data che usi, nella cella B2 contiene il numero di giorni che vuoi aggiungere, usa la formula come questa:
=WORKDAY(A2,B2)
STAMPA Entra chiave per ottenere il risultato.
Aggiungi giorni lavorativi esclusi i giorni festivi
Nella cella A5 è la data che utilizzi, nella cella B5 contiene il numero di giorni che desideri aggiungere, nell'intervallo D5: D8 elenca le festività, utilizza la formula come questa:
=WORKDAY(A5,B5,D5:D8)
STAMPA Entra chiave per ottenere il risultato.
NOTA:
La funzione GIORNO LAVORATIVO accetta sabato e domenica come fine settimana, se i fine settimana sono di sabato e domenica, è possibile applicare la funzione GIORNO LAVORO.INTL, che supporta la specifica dei fine settimana.
Visita GIORNO.LAVORATIVO.INTL per ulteriori dettagli.
Se vuoi sottrarre giorni lavorativi a una data, cambia semplicemente il numero di giorni in negativo nella formula.
2.16 Aggiungere o sottrarre anno, mese, giorni specifici a una data
Se vuoi aggiungere un anno specifico, i giorni del mese a una data, la formula che combina la funzione DATA, ANNO, MESE e GIORNI può farti un favore.
Per aggiungere 1 anno 2 mesi e 30 giorni a una data in A11, utilizzare la formula in questo modo:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
STAMPA Entra chiave per ottenere il risultato.
Se vuoi sottrarre, cambia tutti i segni più (+) in segni meno (-).
2.21 Aggiungere o sottrarre ore/minuti/secondi a una data e ora
Qui vengono fornite alcune formule per aggiungere o sottrarre ore, minuti o secondi a un datetime.
Aggiungi o sottrai ore a un datetime
Supponendo di aggiungere 3 ore a una data e ora (può anche essere un'ora) nella cella A2, utilizzare la formula in questo modo:
=A2+3/24
STAMPA Entra chiave per ottenere il risultato.
Aggiungi o sottrai ore a un datetime
Supponendo di aggiungere 15 minuti a una data e ora (può anche essere un'ora) nella cella A5, utilizzare la formula come questa:
=A2+15/1440
STAMPA Entra chiave per ottenere il risultato.
Aggiungi o sottrai ore a un datetime
Supponendo di aggiungere 20 secondi a una data e ora (può anche essere un'ora) nella cella A8, utilizzare la formula come questa:
=A2+20/86400
STAMPA Entra chiave per ottenere il risultato.
Supponendo che ci sia una tabella di Excel che registra l'orario di lavoro di tutto il personale in una settimana, per sommare l'orario di lavoro totale per il calcolo dei pagamenti, puoi utilizzare SOMMA(intervallo) per ottenere il risultato. Ma in generale, il risultato sommato verrà mostrato come un tempo non superiore a 24 ore come mostra lo screenshot qui sotto, come puoi ottenere il risultato corretto?
In realtà, devi solo formattare il risultato come [hh]:mm:ss.
Fare clic con il pulsante destro del mouse sulla cella del risultato, scegliere formato celle nel menu contestuale e nel popping formato celle dialogo, scegliere Custom dalla bolla e digitare [hh]:mm:ss nella casella di testo nella sezione a destra, fare clic su OK.
Il risultato sommato verrà visualizzato correttamente.
2.23 Aggiungere l'orario di lavoro a una data che esclude il fine settimana e i giorni festivi
Qui fornisce una formula lunga per ottenere la data di fine basata sull'aggiunta di un numero specifico di ore lavorative a una data di inizio ed esclude i fine settimana (sabato e domenica) e i giorni festivi.
In una tabella di Excel, A11 contiene la data e l'ora di inizio e B11 contiene l'orario di lavoro, nella cella E11 ed E13 sono gli orari di inizio e fine del lavoro e la cella E15 contiene la vacanza che verrà esclusa.
Si prega di utilizzare la formula come questa:
=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))
STAMPA Entra chiave per ottenere il risultato.
Se si dispone di Kutools for Excel installato, un solo strumento – Data e ora Aiutor può risolvere la maggior parte dei calcoli sull'aggiunta e la sottrazione di datetime.
1. Fare clic su una cella in cui si desidera visualizzare il risultato e applicare questo strumento facendo clic Kutools > formula Helper > Data e ora aiutante.
2. Nel Data e ora Helper dialogo, controlla Aggiungi Opzione o Sottrarre opzione di cui hai bisogno, quindi scegli la cella o digita direttamente la data e l'ora in cui desideri utilizzare Input di argomenti sezione, quindi specifica gli anni, i mesi, le settimane, i giorni, le ore, i minuti e i secondi che desideri aggiungere o sottrarre, quindi fai clic su Ok. Vedi screenshot:
È possibile visualizzare in anteprima il risultato calcolato nel file Risultato .
Ora il risultato viene emesso, trascina la maniglia automatica su altre celle per ottenere i risultati.
Clicchi Data e ora Helper per saperne di più sull'utilizzo di questa funzione.
Clicchi Kutools for Excel per conoscere tutte le funzionalità di questo componente aggiuntivo.
Clicchi Scarica a pagamento ottenere 30-giorni di prova gratuita di Kutools for Excel
2.41 Controllare o evidenziare se una data è scaduta
Se è presente un elenco di date scadute di prodotti, potresti voler controllare ed evidenziare le date scadute in base a oggi, come mostra lo screenshot qui sotto.
In realtà, il Formattazione condizionale può gestire rapidamente questo lavoro.
1. Seleziona le date che desideri controllare, quindi fai clic su Casa > Formattazione condizionale > Nuova regola.
2. Nel Nuova regola di formattazione finestra di dialogo, selezionare Utilizzare una formula per determinare quali celle formattare nel Seleziona un tipo di regola sezione e digita =B2 nella casella di input (B2 è la prima data che si desidera controllare) e fare clic Formato apparire formato celle finestra di dialogo, quindi scegli una formattazione diversa per far risaltare le date scadute di cui hai bisogno. Clic OK > OK.
2.42 Restituire la fine del mese corrente/il primo giorno del mese successivo/a>
Le date scadute di alcuni prodotti sono alla fine del mese di produzione o al primo giorno del mese di produzione successivo, per elencare rapidamente le date scadute in base alla data di produzione, seguire questa parte.
Ottieni la fine del mese corrente
Ecco una data di produzione nella cella B13, utilizzare la formula come questa:
=EOMONTH(B13,0)
STAMPA Entra chiave per ottenere il risultato.
Ottieni il 1° giorno del mese successivo
Ecco una data di produzione nella cella B18, utilizzare la formula come questa:
=EOMONTH(B18,0)+1
STAMPA Entra chiave per ottenere il risultato.
3. Calcola l'età
In questa sezione vengono elencati i metodi per risolvere come calcolare l'età in base a una data data oa un numero di serie.
3.11 Calcolare l'età in base alla data di nascita indicata
Ottieni l'età in numero decimale in base alla data di nascita
Clicchi FRAZIONE.ANNO per i dettagli sui suoi argomenti e l'uso.
Ad esempio, per ottenere l'età in base all'elenco delle date di nascita nella colonna B2:B9, utilizzare la formula come questa:
=YEARFRAC(B2,TODAY())
STAMPA Entra , quindi trascina il quadratino di riempimento automatico verso il basso finché non vengono calcolate tutte le età.
Mancia:
1) È possibile specificare la posizione decimale di cui hai bisogno nel file formato celle dialogo.
2) Se vuoi calcolare l'età in una data specifica in base a una data di nascita, cambia OGGI() con la data specifica racchiusa tra virgolette doppie come =YEARFRAC(B2,"1/1/2021")
3) Se vuoi ottenere l'età dell'anno successivo in base alla data di nascita, aggiungi semplicemente 1 nella formula come =YEARFRAC(B2,TODAY())+1.
Ottieni l'età in numero intero in base alla data di nascita
Clicchi DATADIF per i dettagli sui suoi argomenti e l'uso.
Utilizzando l'esempio sopra, per ottenere l'età in base alle date di nascita nell'elenco in B2:B9, utilizzare la formula come questa:
=DATEDIF(B2,TODAY(),"y")
STAMPA Entra , quindi trascina il quadratino di riempimento automatico verso il basso fino a quando non vengono calcolate tutte le età.
Mancia:
1) Se vuoi calcolare l'età in una data specifica in base a una data di nascita, cambia TODAY() con la data specifica racchiusa tra virgolette come =DATEDIF(B2,"1/1/2021","y") .
2) Se vuoi ottenere l'età dell'anno successivo in base alla data di nascita, aggiungi semplicemente 1 nella formula come =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calcola l'età nel formato anni, mese e giorni per data di nascita
Se vuoi calcolare l'età in base a una data data di nascita e mostrare il risultato come xx anni, xx mesi, xx giorni come mostra lo screenshot qui sotto, ecco una lunga formula che può aiutarti.
Per ottenere l'età in anni, mesi e giorni in base alla data di nascita nella cella B12, utilizzare la formula in questo modo:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
STAMPA Entra per ottenere l'età, quindi trascina il quadratino di riempimento automatico verso il basso su altre celle.
Mancia:
Se vuoi calcolare l'età in una data specifica in base a una data di nascita, cambia TODAY() nella data specifica racchiusa tra virgolette come = =DATEDIF(B12,"1/1/2021","Y")& " Anni, "&DATEDIF(B12,"1/1/2021","YM")&" Mesi, "&DATEDIF(B12,"1/1/2021","MD")&" Giorni".
3.13 Calcolare l'età per data di nascita prima del 1/1/1900
In Excel, la data precedente al 1/1/1900 non può essere inserita come data/ora o calcolata correttamente. Ma se vuoi calcolare l'età di una persona famosa in base alla data di nascita (prima del 1/11900) e alla data di morte, solo un codice VBA può aiutarti.
1. Stampa altro + F11 tasti per abilitare Microsoft Visual Basic, Applications Edition finestra e fare clic su inserire scheda e scegliere Modulo per creare un nuovo modulo.
2. Quindi copia e incolla il codice seguente nel nuovo modulo.
VBA: calcola l'età prima del 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 e tornare al foglio e selezionare una cella in cui inserire l'età calcolata, digitare =AgeFunc(birthdate,deathdate), in questo caso, =FunzioneEtà(B22,C22), premere il tasto Invio per ottenere l'età. E usa la maniglia di riempimento automatico per applicare questa formula ad altre celle, se necessario.
Se si dispone di Kutools for Excel installato in Excel, puoi applicare il Data e ora Helper strumento per calcolare l'età.
1. Seleziona una cella in cui desideri posizionare l'età calcolata e fai clic Kutools > formula Helper > Data e ora helper.
2. Nel Data e ora Helper dialogo,
- 1) Controllare Età opzione;
- 2) Scegli la cella della data di nascita o inserisci direttamente la data di nascita o fai clic sull'icona del calendario per selezionare la data di nascita;
- 3) Scegli Oggi opzione se vuoi calcolare l'età attuale, scegli Data specificata opzione e inserire la data se si desidera calcolare l'età nel passato o nel futuro;
- 4) Specificare il tipo di output dall'elenco a discesa;
- 5) Visualizza in anteprima il risultato di output. Clic Ok.
Clicchi Data e ora Helper per saperne di più sull'utilizzo di questa funzione.
Clicchi Kutools for Excel per conoscere tutte le funzionalità di questo componente aggiuntivo.
Clicchi Scarica gratis ottenere 30-giorni di prova gratuita di Kutools for Excel
3.31 Ottieni il compleanno dal numero ID
Se è presente un elenco di numeri ID che utilizzano le prime 6 cifre per registrare la data di nascita, ad esempio 920315330, significa che la data di nascita è il 03/15/1992, come puoi inserire rapidamente la data di nascita in un'altra colonna?
Ora prendiamo come istanza l'elenco dei numeri ID che iniziano nella cella C2 e usiamo la formula in questo modo:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
STAMPA Entra chiave. Quindi trascina la maniglia di riempimento automatico verso il basso per ottenere altri risultati.
NOTA:
Nella formula puoi modificare il riferimento in base alle tue esigenze. Ad esempio, se il numero ID visualizzato come 13219920420392, la data di nascita è 04/20/1992, puoi modificare la formula in =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ "&MID(C2,4,4) per ottenere il risultato corretto.
3.32 Calcola l'età dal numero ID
Se esiste un elenco di numeri ID che utilizzano le prime 6 cifre per registrare la data di nascita come 920315330 significa che la data di nascita è 03/15/1992, come puoi calcolare rapidamente l'età in base a ciascun numero ID in Excel?
Ora prendiamo come istanza l'elenco dei numeri ID che iniziano nella cella C2 e usiamo la formula in questo modo:
=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")
STAMPA Entra chiave. Quindi trascina la maniglia di riempimento automatico verso il basso per ottenere altri risultati.
NOTA:
In questa formula, se l'anno è inferiore all'anno corrente, l'anno sarà considerato a partire da 20, ad esempio 200203943 sarà considerato l'anno 2020; se l'anno è maggiore dell'anno corrente, l'anno sarà considerato a partire da 19, ad esempio 920420392 sarà considerato l'anno 1992.
Altri tutorial di Excel:
Combina più cartelle di lavoro/fogli di lavoro in uno
Questo tutorial, elencando quasi tutti gli scenari che potresti incontrare e fornendo soluzioni professionali relative per te.
Dividi celle di testo, numero e data (separate in più colonne)
Questo tutorial è diviso in tre parti: dividere le celle di testo, dividere le celle dei numeri e dividere le celle della data. Ogni parte fornisce esempi diversi per aiutarti a sapere come gestire il lavoro di divisione quando incontri lo stesso problema..
Combina il contenuto di più celle senza perdere dati in Excel
Questo tutorial restringe l'estrazione a una posizione specifica in una cella e raccoglie diversi metodi per estrarre testo o numeri da una cella in base a una posizione specifica in Excel.
Confronta due colonne per corrispondenze e differenze in Excel
Qui questo articolo copre la maggior parte degli scenari possibili del confronto tra due colonne che potresti incontrare e spero che possa aiutarti.
I migliori strumenti per la produttività in ufficio
Kutools for Excel Risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%
- Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
- Unisci celle / righe / colonne e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... Impedisci celle duplicate; Confronta intervalli...
- Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
- Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
- Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
- Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
- Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
- Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
- Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...
- 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, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
