Mastering di data e ora di Excel: numeri di serie, giorni della rete, datetime e altro

Se hai bisogno di calcolare le date nei tuoi fogli di calcolo, Excel usa il proprio sistema unico, che chiama seriale Numeri. Ogni data (mese, giorno, anno) e ora (ora, minuto e secondo) ha un numero di serie equivalente che risale al 1 gennaio 1900 alle 00:00:00 (ore, minuti, secondi). Da non confondere con le date di Julian, quali sono i mainframe e molti linguaggi di programmazione, i numeri di serie di Excel sono esclusivi dei prodotti Microsoft.

Apri Excel e inserisci i numeri da 1 a 5 nelle celle da A3 a A7. Evidenzia l'intervallo, quindi dal gruppo Numero della scheda Home seleziona Data breve dall'elenco a discesa. Si noti che i numeri da 1 a 5 sono stati modificati nelle seguenti date: da 1/1/1900 a 1/5/1900.

JD Sartain

Immettere ora le date nelle celle da B3 a B7 per i primi cinque giorni di maggio ; 5/1/2016 fino al 5/5/2016. Evidenzia l'intervallo; quindi dal gruppo Numero della scheda Home, selezionare Generale dall'elenco a discesa. Notare che le cinque date sono state modificate con i seguenti numeri: da 42491 a 42495.

[Ulteriori letture: il tuo nuovo PC ha bisogno di questi 15 programmi eccellenti gratuiti]

Questi sono gli equivalenti numerici dei primi cinque giorni di maggio di 2016. Excel li chiama numeri di serie e li utilizza per calcolare date e ore.

Quindi, inserisci la seguente formula nella colonna C per convertire le date dei numeri di serie di Excel in date giuliane:

= DESTRA (ANNO (B1), 2) e B1-DATE (ANNO (B1), 1,0) . Questa è una formula utile se devi esportare i dati del foglio di calcolo con le date in un altro programma come un'applicazione di database o data / ora.

Questa formula converte le date di Julian in date seriali: = DATE (1900 + INT (Julian_Date / 1000), 1, MOD (Julian_Date, 1000))

Numero di giorni tra due date

Magnum PI addebita $ 200 al giorno più le spese per i suoi servizi come investigatore privato. Vuole sapere quanti soldi guadagnerà se lavora tutti i giorni dal 21 febbraio 2016 al 1 maggio 2016. Invece di calcolare manualmente questo lasso di tempo (tenendo conto del numero di giorni al mese, Leap Year, ecc.), può usare Excel per risolvere rapidamente questo problema.

La formula per questo calcolo è semplice: basta sottrarre la data di fine dalla data di inizio:

= SOMMA (B1-A1) . La risposta è di 70 giorni. Ora aggiungi la tariffa giornaliera di Magnum alla fine di questa formula:

= SOMMA (B1-A1) * 200 e ottieni la risposta in una formula semplice: $ 14.000,00. Ciò equivale a circa 25 dollari l'ora di salari insignificanti anche per gli anni ottanta.

JD Sartain

Ora Magnum vuole sapere quanto guadagnerà se lavora alle stesse date, ma si prende i fine settimana. Immettere questa formula in C3: = NETWORKDAYS (A3, B3) . La nuova risposta è di 50 giorni. Oppure selezionare Data e ora dalla scheda Formule, fare clic su GIORNI NETWORK e viene visualizzata la finestra Argomenti funzione. Immettere la data di inizio e la data di fine nelle caselle di campo o fare clic sulle celle corrispondenti (puntare ad esse) sul foglio di calcolo, quindi fare clic su OK . La stessa risposta: 50 giorni. Ora aggiungi la tariffa giornaliera di Magnum alla fine di questa formula: = NETWORKDAYS (A3, B3) * 200 e ottieni il nuovo stipendio in un passo base: $ 10.000,00.

JD Sartain

Converti testo date per date effettive

Quando le date vengono copiate in Excel, vengono spesso copiate come testo. L'importazione delle date è un'opzione migliore ma, a volte, anche le date importate vengono inserite come testo. In questo caso, utilizzare la funzione DATEVALUE per convertire le date di testo in numeri di serie di Excel. Per verificare questa funzione, immettere cinque date, come testo, nelle celle dalla A5 alla A9. Inserisci un apostrofo prima di ogni data e questo convertirà la data in una voce di testo.

NOTA: Quando le date vengono copiate o importate in Excel come campi di testo (da un file ASCII, da un database o persino da un file di testo ), il sistema li legge automaticamente come testo; tuttavia, se si copia una data da MS Word (utilizzando copia / incolla / testo), Excel la riconosce come una data.

Muovi il cursore su B5. Dalla scheda Formule, selezionare Data e ora > DATEVALUE . Viene visualizzata la schermata Argomenti della funzione . Non è necessario reinserire la data nella casella di campo Date_text . Basta fare clic sulla data di testo nella cella A3 e il numero di serie di Excel appare nella cella B5. Copia la formula in basso a B9.

JD Sartain

Ora, tutto ciò che devi fare è formattare le celle in base al formato di data che preferisci. Evidenzia le celle da B5 a B9. Dal gruppo Numero della scheda Home, selezionare un formato numerico dall'elenco a discesa Generale . Puoi anche fare clic sulla piccola freccia nell'angolo in basso a destra di questo gruppo e si apre la finestra Formato celle . Selezionare Data dalla Categoria (pannello di sinistra). Scorri le opzioni della data nel riquadro Tipo e scegli quella che si adatta al tuo progetto. Ora i numeri di serie di Excel vengono visualizzati come date effettive, il che significa che è possibile utilizzare le formule per eseguire calcoli.

JD Sartain

Numeri di serie per tutti i formati di data

Tutte le aziende utilizzano database e fogli di calcolo per calcolare date e orari giornalieri per paghe, spese, inventario, rapporti sulle vendite, contabilità e altro.

JD Sartain

Excel fornisce i numeri di serie per tutti i formati di data e ora, da mesi a singoli secondi. Controlla tu stesso: posiziona il cursore sulla cella A1. Vai a Formule > Funzioni e seleziona Data e ora > ORA . Quindi fare clic su OK nella finestra popup.

Dal gruppo Celle della scheda Home, selezionare Formato > Formattare celle . Nella finestra Formato celle , selezionare un formato data personalizzato con minuti e secondi, quindi fare clic su OK .

Quindi, convertire la data in un numero di serie Excel (come abbiamo fatto sopra , selezionare il formato Generale dal gruppo Numero della scheda Pagina iniziale.

Le seguenti formule convertono il numero di serie in giorno della settimana, mese, anno, ora, minuti e secondi. Ecco come.

Vai a Formule > Libreria funzioni e seleziona Data e ora > GIORNO . Quando si apre la finestra Argomenti della funzione , puntare sulla cella A1 o immettere A1 nella casella del campo Numero seriale. Excel visualizza quindi solo il giorno del mese, in questo caso il 9

JD Sartain

Selezionare le seguenti formule dalla scheda Formule, gruppo Libreria funzioni, Data e ora per le celle da B2 a B6: MESE, ANNO , ORA, MINUTO, SECONDO o immettere le seguenti formule:

= MESE (A1) restituisce il mese di oggi

= ANNO (A1) restituisce l'anno di oggi

= HOUR (A1) restituisce l'ora odierna

= MINUTE (A1) restituisce il minuto di oggi

= SECONDO (A1) restituisce il secondo

JD Sartain di oggi

Tutte queste formule possono essere utilizzate separatamente o con altre funzioni di Data e Ora per calcolare tutto da giorni per trimestre; a quante settimane o giorni tra ora e Natale; a quante ore nel mese di febbraio in un anno bisestile.

Excel Windows vs Excel Mac

Windows Excel utilizza il sistema di data 1900 (un riporto da Lotus 1-2-3), mentre Excel per l'Apple Mac usa il sistema data del 1904. Quindi il numero di serie 1 in Windows Excel è uguale a 1/1/1900, 12:00 am, mentre in Mac Excel è 1/2/1904, 12:00 am. Sembra che Microsoft avrebbe dovuto risolvere il problema, ma è ancora un problema. Il trasferimento di file tra i due programmi dovrebbe funzionare perché il sistema di data è memorizzato in ogni file. Ma copia e incolla è una storia diversa. Differenti sistemi di data su piattaforme diverse risulteranno in date errate, che saranno disattivate di quattro anni e un giorno.

Per rendere compatibile il sistema di data Excel 1904 di Apple Mac con il sistema di data Excel di Windows Excel 1900:

JD Sartain

Dal menu Backstage della scheda File, selezionare Opzioni > Avanzate . Scorri verso il basso la finestra fino a visualizzare Quando calcola questa cartella di lavoro (il decimo sottotitolo in basso nella pagina). Seleziona la casella che dice: Usa sistema data 1904, quindi fai clic su OK .

Ci sono una dozzina (o più) di questi tipi di funzioni in Excel. Restate sintonizzati per la seconda parte, che copre le restanti formule di data e ora con esempi.