Se devi eseguire operazioni con date nel tuo lavoro, le funzionalità di Excel ti aiuteranno a semplificare il tuo lavoro. È possibile eseguire varie operazioni sulle date. È possibile modificare i formati della data (utilizzando la scheda Numero della finestra di dialogo Formato cella), ordinarli in ordine crescente o decrescente. Le date possono essere fatte operazioni aritmetiche. Ad esempio, per ottenere una data futura, puoi aggiungere un determinato numero di giorni alla data corrente. Oppure, se sottrai un altro da una data, puoi determinare il numero di giorni trascorsi tra di loro.
Ha i seguenti argomenti. Anno - rappresenta l'anno della data. Mancia. Per evitare confusione, fornire sempre anni a quattro cifre. Month è un numero intero che rappresenta il mese dell'anno, da 1 a 12. Day è un numero intero che rappresenta il giorno del mese, da 1 a.
Sottrai direttamente una data da un'altra
Quando analizzi le informazioni sulla data nei tuoi fogli di lavoro, probabilmente eseguirai alcune operazioni aritmetiche su quelle date. Quindi, in realtà stai sottraendo due numeri, e la normale operazione aritmetica funziona senza intoppi. Scopriamo perché questo sta accadendo.
Esempio 1
1. Per sapere da quanto tempo un dipendente ha lavorato in azienda, è necessario sottrarre la data del suo arrivo al lavoro dalla data corrente. Se il tuo foglio di lavoro contiene date di assunzione, è una buona idea aggiungere la data odierna al foglio di lavoro. (Il modo più semplice per inserire la data corrente è con la combinazione di tasti CTRL SHIFT;).
Sottrai la data dalla data corrente
Quando sottrai una data successiva da una data precedente, l'operazione di sottrazione restituisce un numero negativo esattamente come dovrebbe. Per sottrarre una data dalla data odierna, puoi utilizzare una delle formule precedenti. Ora che sai come sottrarre due date, vediamo come aggiungere o sottrarre giorni, mesi o anni per una data specifica.
Se hai una data in una cella o un elenco di date in una colonna, puoi aggiungere o sottrarre un certo numero di giorni per quelle date usando l'operazione aritmetica appropriata. La formula generale per aggiungere un numero specificato di giorni a una data è la seguente.
2. Attivare la cella in cui il file anzianità questo impiegato.
3. Ora dalla data corrente è necessario sottrarre la data di assunzione. In questo esempio, puoi prima provare a utilizzare la formula =$B$2-C5. Prestare attenzione al risultato! È andata così grande numero, perché la formula calcola il numero di giorni (non anni) di lavoro nell'impresa.
La data può essere inserita in diversi modi. Lo screenshot seguente mostra le formule di cui sopra in azione. La nota. Il risultato delle formule di cui sopra è un numero di serie che indica la data. Nella scheda Numero selezionare Data dall'elenco Categoria e quindi selezionare il formato della data desiderato.
Come aggiungere o sottrarre settimane fino ad oggi
Nel cap. Per sottrarre un determinato numero di giorni da una data specifica, si esegue nuovamente una normale operazione aritmetica. Ecco alcuni esempi di formule. Ora vediamo cosa fa effettivamente la funzione. La logica della formula è ovvia e comprensibile. Ovviamente nulla vieta di inserire il segno meno direttamente nella formula per sottrarre i mesi da una data.
4. Dividendo i risultati per 365, otteniamo la risposta in anni. Nel nostro caso, la formula dovrebbe assomigliare a =($B$2-C5)/365. Ora è facile notare che il primo dipendente ha lavorato nell'azienda per più di 22 anni.
In questa lezione imparerai varie formule utili per aggiungere e sottrarre date in Excel. Ad esempio, imparerai come sottrarre un'altra data da una data, come aggiungere diversi giorni, mesi o anni a una data e così via.
Somma e sottrazione di anni da una data
E, naturalmente, puoi inserire il numero di mesi da aggiungere o sottrarre in una formula invece di fare riferimento a una cella. Le formule reali potrebbero assomigliare a questa. Mesi: il numero di mesi da aggiungere o sottrarre. . Consulta le istruzioni dettagliate.
Le formule potrebbero avere questo aspetto. Per sottrarre anni, mesi, giorni. Quando viene applicata alla nostra colonna della data, la formula assume la seguente forma. Ogni funzione esegue un'operazione semplice e, combinando più funzioni all'interno di un'unica formula, è possibile risolvere problemi più complessi e complessi. In questa parte finale, riassumeremo ciò che abbiamo appreso e forniremo collegamenti a varie formule di esempio per aiutarti a trovare la funzione più adatta per calcolare le tue date. Ad esempio, per aggiungere 7 giorni alla data corrente, inserisci la seguente formula in una cella.
Se hai già preso lezioni su come lavorare con le date in Excel (la nostra o qualsiasi altra lezione), allora dovresti conoscere le formule per il calcolo delle unità di tempo, come giorni, settimane, mesi, anni.
Quando si analizzano le date in qualsiasi dato, è spesso necessario eseguire operazioni aritmetiche su queste date. Questo articolo spiegherà alcune formule per aggiungere e sottrarre date che potresti trovare utili.
Per aggiungere 30 giorni a oggi, esclusi i fine settimana, utilizzare quanto segue. Inoltre, non ha argomenti. Se vuoi visualizzare la data odierna e l'ora corrente sul tuo foglio di lavoro, inserisci la seguente formula in una cella. Può trattarsi di un riferimento di cella, di una data immessa utilizzando o restituita da altre formule. Molto spesso lo utilizzerai in combinazione con altre funzioni, come mostrato negli esempi seguenti.
L'elenco completo dei tipi di restituzione disponibili è disponibile di seguito Guida allo studio. L'intervallo di tempo per calcolare la differenza di data dipende dalla lettera immessa nell'ultimo argomento. Esclude automaticamente i fine settimana dai calcoli, così come qualsiasi altro vacanze che specifichi.
Come sottrarre le date in Excel
Supponiamo che nelle tue celle A2 E B2 contiene date e devi sottrarre una data da un'altra per scoprire quanti giorni ci sono tra di loro. Come spesso accade in Excel, questo risultato può essere ottenuto in diversi modi.
Esempio 1: sottrazione diretta di una data da un'altra
Penso che tu sappia che Excel memorizza le date come numeri interi a partire da 1, che è il 1 gennaio 1900. Quindi puoi semplicemente sottrarre un numero da un altro aritmeticamente:
Questa funzione è molto utile per calcolare traguardi e altri eventi importanti sulla base di un calendario lavorativo standard. Naturalmente, è difficile cogliere l'essenza di questa breve spiegazione, ma altri esempi illustrati con schermate renderanno le cose molto semplici.
Esclude automaticamente i fine settimana e possibilmente i giorni festivi. Restituisce anche il numero di giorni della settimana compresi tra due date, ma consente di specificare quali giorni devono essere considerati festivi. Quindi cos'è un numero di serie? Ok, cominciamo dall'inizio.
Come funziona la formula
Immettere 1 in qualsiasi cella e quindi formattarlo come data. Il primo giorno del sistema di calendario. Digita 2 in una cella e formattala come data. Puoi anche prendere una cella che contiene una data e formattarla come numero. Metti quel numero in una cella e poi formattalo come un numero o un generatore.
Esempio 2. Sottrazione di date utilizzando la funzione RAZDAT
Se la formula precedente ti sembra troppo semplice, lo stesso risultato può essere ottenuto in modo più sofisticato utilizzando la funzione RAZDAT(DATASE).
RAZNDAT(A2;B2;"d")
=DATA.SE(A2;B2;"d")
La figura seguente mostra che entrambe le formule restituiscono lo stesso risultato, ad eccezione della riga 4, dove la funzione RAZDAT(DATEDIF) restituisce un errore #NUMERO!(#NUM!). Vediamo perché questo accade.
Come aggiungere e sottrarre tempo in Excel
Vedrai il numero 42. Quando calcoli la differenza tra due date per sottrazione, il risultato sarà il numero di giorni tra le due date. Non entreremo in tutte le funzioni della data qui, ma capire che un numero di serie rappresenta un giorno ti darà una buona base per lavorare con le date.
Hai mai lavorato con date che contengono valori temporali? Quando converti una data con l'ora in formato numerico, vedrai un numero decimale. Questo numero decimale fa parte del giorno. Tuttavia, ci sono diversi modi per risolvere questo problema. Questo risultato è purtroppo infelice. Nello specifico, vuoi ordinarli ed eseguire l'aritmetica della data.
Quando sottrai una data successiva (6 maggio 2015) da una data precedente (1 maggio 2015), l'operazione di sottrazione restituisce un numero negativo. Tuttavia, la sintassi della funzione RAZDAT(DATEDIF) non lo consente data d'inizio era di più data di fine e, naturalmente, restituisce un errore.
Esempio 3: sottrarre una data dalla data corrente
Per sottrarre una data specifica dalla data corrente, è possibile utilizzare una qualsiasi delle formule descritte in precedenza. Basta usare la funzione al posto della data odierna OGGI(OGGI):
Come sottrarre le date in Excel
Tuttavia, devi essere consapevole dei potenziali problemi delle prime date. Il mondo oggi usa un sistema di datazione diverso da quello che usavamo qualche centinaio di anni fa. Oggi usiamo il calendario gregoriano; in precedenza gran parte del mondo occidentale utilizzava variazioni regionali del calendario giuliano.
Oggi, un'opzione sembra molto strana. Peggio ancora, diverse parti del mondo sono passate dal giuliano al gregoriano tempo diverso per diversi secoli. Di conseguenza, i registri di spedizione possono mostrare che sono arrivati in un porto prima di lasciare il porto precedente.
OGGI()-A2
=OGGI()-A2
RAZDAT(A2;OGGI();"d")
=DATA.SE(A2;OGGI();"d")
Come nell'esempio precedente, le formule funzionano correttamente quando la data corrente è maggiore della data sottratta. Altrimenti, la funzione RAZDAT(DATEDIF) restituisce un errore.
Esempio 4: sottrazione di date utilizzando la funzione DATE
Se preferisci inserire le date direttamente nella formula, usa la funzione DATA(DATA) e quindi sottrarre una data da un'altra.
Per illustrare queste differenze regionali negli Stati Uniti, il calendario perpetuo di Toque Norby lo spiega. Texas, Florida, California, Nevada, Arizona e New Mexico convertiti con la Spagna. Ultimo giorno di luglio: 4 ottobre, primo giorno gregoriano: 15 ottobre, ultimo giorno giuliano: 9 dicembre, primo giorno gregoriano: 20 dicembre, Washington, Oregon e la costa orientale furono convertite in Inghilterra. Ultima data giuliana: 2 settembre, prima data gregoriana: sett. 14. . In tutti i casi, si noti che non ci sono mai state date fisse per le tre regioni.
Funzione DATA ha la seguente sintassi: DATA DI( anno; mese; giorno) .
Ad esempio, la formula seguente sottrae il 15 maggio 2015 dal 20 maggio 2015 e restituisce la differenza - 5 giorni.
DATA(2015;5;20)-DATA(2015;5;15)
=DATA(2015;5;20)-DATA(2015;5;15)
Se necessario contare il numero di mesi o anni tra due date, quindi la funzione RAZDAT(DATEDIF) - l'unico Possibile soluzione. Nel seguito dell'articolo troverai diversi esempi di formule che rivelano questa funzione in dettaglio.
Per illustrare, supponiamo di voler lavorare con queste date in foglio di calcolo. Qui abbiamo almeno quattro problemi.
- Una di queste date non è mai esistita.
- La maggior parte delle date viene prima.
- Usano diversi formati di data.
- Alcune date sono vicine.
Poiché ogni numero di serie della data rappresenta un giorno, i numeri semplificano i calcoli del tempo. Diamo un'occhiata alla prima riga di dati in questa tabella. La prima versione è divisa in due righe. Tuttavia, la formula sarà molto complessa. Questi anni ottengono un giorno in più solo se sono equamente divisibili per.
Ora che sai come sottrarre una data da un'altra, vediamo come puoi aggiungere o sottrarre un certo numero di giorni, mesi o anni da una data. Per questo, ce ne sono diversi Funzioni di Excel. Quale scegliere dipende dalle unità di tempo che si desidera aggiungere o sottrarre.
Come aggiungere (sottrarre) giorni a una data in Excel
Se hai una data in una cella o un elenco di date in una colonna, puoi aggiungere (o sottrarre) un certo numero di giorni utilizzando l'operazione aritmetica appropriata.
Usiamo le stesse date di cui sopra. Ad ogni data viene assegnato un numero di serie univoco. Fortunatamente, non è necessario saperlo, ma il fatto che tutte le date siano numeriche può essere molto utile. Per trovare il numero seriale di una data, seleziona la cella contenente la data, quindi apri la finestra di dialogo Formato celle espandendo la sezione Numero nella scheda Home oppure apri il menu Formato e seleziona Celle.
Il numero di serie della data apparirà nel campo "Esempio" sulla destra. Puoi usare questi numeri diversi modi. È possibile aggiungere un numero a una data per indicare una data successiva di qualche giorno o sottrarre un numero per ottenere una data precedente. Puoi prendere una data da un'altra per scoprire quanti giorni ci sono tra di loro. Ci sono molte funzioni di data pronte all'uso.
Esempio 1: aggiungere giorni a una data in Excel
La formula generale per aggiungere un certo numero di giorni a una data è la seguente:
= data + N giorni
La data può essere impostata in diversi modi:
Riferimento cella:
Chiamare una funzione DATA(DATA):
DATA(2015;5;6)+10
=DATA(2015;5;6)+10
Chiamare un'altra funzione. Ad esempio, per aggiungere più giorni alla data corrente, utilizzare la funzione OGGI(OGGI):
L'età di una persona è il tempo trascorso dalla sua nascita. Quindi tutto ciò che dobbiamo fare è inserire la data odierna in una cella e la data di nascita della persona nell'altra cella, quindi rimuovere la data di nascita da oggi e ottieni la tua età, giusto? Poiché hai preso la data del numero di serie da un'altra data, ottieni il numero di giorni tra di loro.
Nei calcoli della data, la cella del risultato viene formattata automaticamente come la prima cella della formula. Dobbiamo convertire questo numero di giorni in diversi anni. La maggior parte degli anni ha 365 giorni, ma ogni quattro anni ha 366 giorni. Quindi, il numero medio di anni.
OGGI()+10
=OGGI()+10
La figura seguente mostra l'effetto di queste formule. Al momento della scrittura, la data corrente era il 6 maggio 2015.
Nota: Il risultato di queste formule è un numero intero che rappresenta la data. Per mostrarlo come data, devi selezionare la cella (o le celle) e fare clic ctrl+1. Si aprirà una finestra di dialogo Formato cella(Formato celle). Sulla scheda Numero(Numero) nell'elenco dei formati numerici, selezionare data(Data) e quindi specificare il formato necessario. Di più descrizione dettagliata Troverete nell'articolo.
Notare le parentesi intorno alla prima parte della formula. Esegui moltiplicazioni e divisioni prima di eseguire addizioni e sottrazioni, ma tutto ciò che è tra parentesi viene eseguito per primo. Ora possiamo vedere alcuni anni, ma non è ancora del tutto corretto. Otteniamo il risultato esatto, ma non vogliamo davvero vedere questa fazione. Questo è meglio che cambiare il numero di cifre decimali visualizzate, che può portare all'arrotondamento del numero di numeri e alla pressione di un risultato errato.
Inserisci data Oggi automaticamente
Puoi risparmiare tempo inserendo manualmente la data odierna.
Quanto devi essere preciso
Questa formula fornisce risultati abbastanza accurati, ma non è infallibile. Dividere per il numero medio di giorni in un anno funziona per la maggior parte delle persone la maggior parte del tempo, ma a volte si sbaglia.Esempio 2: sottrarre giorni da una data in Excel
Per sottrarre un certo numero di giorni da una data, è necessario utilizzare nuovamente la normale operazione aritmetica. L'unica differenza rispetto all'esempio precedente è un meno invece di un più :-)
= data - N giorni
Ecco alcuni esempi di formule:
A2-10
=DATA(2015;5;6)-10
=OGGI()-10
Come aggiungere (sottrarre) diverse settimane a una data
Quando vuoi aggiungere (sottrarre) alcune settimane a data specifica, Puoi usare le stesse formule di prima. Devi solo moltiplicare il numero di settimane per 7:
Ma se hai intenzione di farlo, puoi farlo bene! Quindi, come possiamo ottenere una cifra corretta, esatta e garantita? Per calcolare con precisione l'età di una persona, è necessario conoscere l'anno in cui è nata e se ha compiuto o meno un compleanno. Se hanno avuto il loro compleanno, sottrai il loro anno di nascita dall'anno in corso. eppure hai compiuto gli anni, sottrai il loro anno di nascita dall'anno in corso e poi sottrai. Lo facciamo sempre senza pensarci.
Ma cosa succede se non passiamo attraverso il mese in cui sono nati. Potremmo esserlo questo mese, altrimenti potremmo non averlo ancora raggiunto. Ma se non fosse il mese in cui sono nati. Non lo troverai nella Guida o non è elencato nello strumento Inserisci funzione. Test di logica: questa è una domanda o una circostanza a cui si può rispondere "sì" o "no", oppure "vero" o "falso".
Aggiungi N settimane ad oggi in excel:
A2+ N settimane * 7
Ad esempio, per aggiungere 3 settimane a una data in una cella A2, utilizzare la seguente formula:
Sottrai N settimane dalla data in excel:
A2 - N settimane * 7
Per sottrarre 2 settimane dalla data odierna, utilizza questa formula:
OGGI()-2*7
=OGGI()-2*7
Come aggiungere (sottrarre) diversi mesi a una data in Excel
Per aggiungere (o sottrarre) un certo numero di mesi a una data, è necessario utilizzare la funzione DATA(DATA) o DATAMES(EDATA) come mostrato di seguito.
Esempio 1: aggiunta di diversi mesi a una data utilizzando la funzione DATE
Se l'elenco delle date è, ad esempio, in una colonna UN, indica il numero di mesi che vuoi aggiungere (numero positivo) o sottrarre (numero negativo) in qualche cella, diciamo, in C2.
Digita nella cella B2 la formula sottostante, fare clic sull'angolo evidenziato della cella e trascinarlo lungo la colonna con il mouse B all'ultima cella riempita nella colonna UN. Formula dalla cella B2 verrà copiato in tutte le celle della colonna B.
DATA(ANNO(A2),MESE(A2)+$C$2,GIORNO(A2))
=DATA(ANNO(A2),MESE(A2)+$C$2,GIORNO(A2))
Vediamo cosa fa questa formula. La logica della formula è chiara ed evidente. Funzione DATA DI( anno; mese; giorno) riceve i seguenti argomenti:
- Anno dalla data nella cella A2;
- Mese dalla data nella cella A2+ il numero di mesi specificato nella cella C2;
- Giorno dalla data nella cella A2;
Tutto è semplice! Se entri in C2 un numero negativo, la formula sottrarrà mesi, non aggiungerà.
Naturalmente, nulla vieta di inserire un meno direttamente nella formula per sottrarre i mesi:
DATA(ANNO(A2),MESE(A2)-$C$2,GIORNO(A2))
=DATA(ANNO(A2),MESE(A2)-$C$2,GIORNO(A2))
E, naturalmente, puoi specificare il numero di mesi da aggiungere o sottrarre direttamente nella formula senza un riferimento di cella. Le formule finite saranno simili a questa:
aggiungere mesi ad oggi:
DATA(ANNO(A2),MESE(A2)+2,GIORNO(A2))
=DATA(ANNO(A2),MESE(A2)+2,GIORNO(A2))
sottrarre mesi dalla data:
DATA(ANNO(A2),MESE(A2)-2,GIORNO(A2))
=DATA(ANNO(A2),MESE(A2)-2,GIORNO(A2))
Esempio 2: aggiunta o sottrazione di mesi da una data utilizzando la funzione DATE
Excel ha una funzione speciale che restituisce una data che è un certo numero di mesi fa o prima di una determinata data: questa è la funzione DATAMES(EDATO). È disponibile in ultime versioni Excel 2007, 2010, 2013 e il nuovo Excel 2016.
Usando DATAMES(EDATE) Fornisci i seguenti due argomenti:
- Data d'inizio - la data a partire dalla quale viene conteggiato il numero di mesi.
- Mesi è il numero di mesi da aggiungere (numero positivo) o sottrarre (numero negativo).
Queste formule daranno lo stesso risultato delle formule con la funzione DATA(DATA) nell'esempio precedente:
Quando si utilizza la funzione DATAMES(EDATA) la data di inizio e il numero di mesi possono essere specificati direttamente nella formula. Le date possono essere impostate utilizzando la funzione DATA(DATA) o per effetto di altre formule. Per esempio:
Questa formula aggiunge 10 mesi al 7 maggio 2015
DATA(DATA(2015;5;7);10)
=DATA(DATA(2015;5;7);10)
Questa formula sottrae 10 mesi dalla data odierna
DATE(OGGI();-10)
=DATA(OGGI(),-10)
Nota: Funzione DATAMES(EDATE) restituisce solo un numero intero. Per rappresentarlo come una data, è necessario applicare un formato di data alla cella. Come farlo è indicato nell'articolo. Come modificare il formato della data in Excel.
Come aggiungere (sottrarre) anni a una data in Excel
L'aggiunta di anni alle date in Excel equivale all'aggiunta di mesi. Devi usare di nuovo la funzione DATA(DATA), ma questa volta è necessario specificare il numero di anni che si desidera aggiungere:
DATA(ANNO( data) + N anni; MESE( data); GIORNO( data))
= DATA(ANNO( data) + N anni, MESE( data), GIORNO( data))
Su un foglio Excel, le formule potrebbero apparire così:
Aggiungi 5 anni alla data specificata nella cella A2:
DATA(ANNO(A2)+5,MESE(A2),GIORNO(A2))
=DATA(ANNO(A2)+5;MESE(A2);GIORNO(A2))
Sottrai 5 anni dalla data specificata nella cella A2:
DATA(ANNO(A2)-5,MESE(A2),GIORNO(A2))
=DATA(ANNO(A2)-5;MESE(A2);GIORNO(A2))
Per ottenere una formula generica, puoi inserire il numero di anni in una cella e quindi fare riferimento a quella cella nella formula. Un numero positivo aggiungerà anni alla data, mentre un numero negativo sottrarrà.
Aggiunta (sottrazione) di giorni, mesi e anni a una data
Se leggi attentamente i due esempi precedenti, penso che tu abbia indovinato come aggiungere (o sottrarre) anni, mesi e giorni a una data contemporaneamente in una formula. Sì, con l'aiuto della buona vecchia funzione DATA(DATI)!
Per aggiunte X anni, Y mesi e Z giorni:
DATA(ANNO( data) + X anni; MESE( data) + Y mesi; GIORNO( data) + Z giorni)
= DATA(ANNO( data) + X anni, MESE( data) + Y mesi, GIORNO( data) + Z giorni)
Per sottrazione X anni, Y mesi e Z giorni:
DATA(ANNO( data) - X anni; MESE( data) - Y mesi; GIORNO( data) - Z giorni)
= DATA(ANNO( data) - X anni, MESE( data) - Y mesi, GIORNO( data) - Z giorni)
Ad esempio, la seguente formula aggiunge 2 anni e 3 mesi e sottrae 15 giorni dalla data nella cella A2:
DATA(ANNO(A2)+2,MESE(A2)+3,GIORNO(A2)-15)
=DATA(ANNO(A2)+2;MESE(A2)+3;GIORNO(A2)-15)
Per la nostra colonna della data, la formula è simile a questa:
DATA(ANNO(A2)+$C$2;MESE(A2)+$G$2;GIORNO(A2)+$E$2)
=DATA(ANNO(A2)+$C$2,MESE(A2)+$G$2,GIORNO(A2)+$E$2)
Come aggiungere e sottrarre tempo in Excel
IN Microsoft Excel Puoi aggiungere e sottrarre il tempo usando la funzione TEMPO(TEMPO). Consente di trattare le unità di tempo (ore, minuti e secondi) allo stesso modo di anni, mesi e giorni nella funzione DATA(DATA).
aggiungere tempo su excel:
A2 + TEMPO( orologio; minuti; secondi)
= A2 + TEMPO( orologio, minuti, secondi)
sottrarre tempo su excel:
A2 - TEMPO( orologio; minuti; secondi)
= A2 - TEMPO( orologio, minuti, secondi)
Dove A2è la cella con l'ora da modificare.
Ad esempio, per aggiungere 2 ore 30 minuti e 15 secondi al tempo in una cella A2 devi usare la seguente formula:
A2+TEMPO(2;30;15)
=A2+TEMPO(2,30,15)