Pulizia dei dati contenenti valori di data

I dati ottenuti da origini diverse potrebbero contenere valori di data. In questo capitolo si capirà come preparare i dati che contengono valori di dati per l'analisi.

Imparerai a conoscere -

  • Formati data
    • Data in formato seriale
    • Data in diversi formati mese-giorno-anno
  • Conversione delle date in formato seriale in formato mese-giorno-anno
  • Conversione delle date nel formato mese-giorno-anno in formato seriale
  • Ottenere la data odierna
  • Trovare un giorno lavorativo dopo giorni specificati
  • Personalizzazione della definizione di un fine settimana
  • Numero di giorni lavorativi tra due date specificate
  • Estrazione di anno, mese, giorno dalla data
  • Estrazione del giorno della settimana dalla data
  • Ottenere la data da anno, mese e giorno
  • Calcolo del numero di anni, mesi e giorni tra due date

Formati data

Excel supporta Date valori in due modi:

  • Formato seriale
  • In diversi formati anno-mese-giorno

Puoi convertire -

  • UN Date in formato seriale in un file Date nel formato anno-mese-giorno

  • UN Date nel formato anno-mese-giorno su a Date in formato seriale

Data in formato seriale

UN Date in formato seriale è un numero intero positivo che rappresenta il numero di giorni tra la data specificata e il 1 ° gennaio 1900. Sia l'attuale Datee 1 gennaio 1900 sono inclusi nel conteggio. Ad esempio, 42354 è un fileDate che rappresenta il 16/12/2015.

Data nei formati mese-giorno-anno

Excel supporta diversi Date Formati basati su Locale(Posizione) che scegli. Quindi, devi prima determinare la compatibilità del tuo fileDateformati e l'analisi dei dati a portata di mano. Nota che certoDate formati sono preceduti da * (asterisco) -

  • Date i formati che iniziano con * (asterisco) rispondono alle modifiche nelle impostazioni internazionali di data e ora specificate per il sistema operativo

  • Date formati senza * (asterisco) non sono influenzati dalle impostazioni del sistema operativo

A scopo di comprensione, puoi assumere gli Stati Uniti come impostazioni internazionali. Trovate quanto segueDate formati da scegliere per il file Date- 8 ° giugno 2016 -

  • * 6/8/2016 (influenzato dalle impostazioni del sistema operativo)
  • * Mercoledì 8 giugno 2016 (influenzato dalle impostazioni del sistema operativo)
  • 6/8
  • 6/8/16
  • 06/08/16
  • 8-Jun
  • 8-Jun-16
  • 08-Jun-16
  • Jun-16
  • June-16
  • J
  • J-16
  • 6/8/2016
  • 8-Jun-2016

Se inserisci solo due cifre per rappresentare un anno e se:

  • Le cifre sono 30 o superiori, Excel presume che le cifre rappresentino gli anni del ventesimo secolo.

  • Le cifre sono inferiori a 30, Excel presume che le cifre rappresentino gli anni nel ventunesimo secolo.

Ad esempio, 1/1/29 viene considerato come 1 gennaio 2029 e 1/1/30 viene considerato come 1 gennaio 1930.

Conversione delle date in formato seriale in formato mese-giorno-anno

Per convertire le date dal formato seriale al formato mese-giorno-anno, seguire i passaggi indicati di seguito:

  • Clicca il Number scheda nel file Format Cells la finestra di dialogo.

  • Clic Date sotto Category.

  • Selezionare Locale. Il disponibileDate formati verranno visualizzati come un elenco sotto Type.

  • Fare clic su un file Format sotto Type per guardare l'anteprima nel riquadro adiacente a Sample.

Dopo aver scelto il formato, fare clic su OK.

Conversione delle date nel formato mese-giorno-anno in formato seriale

Puoi convertire le date nel formato Mese-Giorno-Anno in formato Seriale in due modi:

  • Utilizzando Format Cells la finestra di dialogo

  • Utilizzando Excel DATEVALUE funzione

Utilizzo della finestra di dialogo Formato celle

  • Clicca il Number scheda nel file Format Cells la finestra di dialogo.

  • Clic General sotto Category.

Utilizzo della funzione DATEVALUE di Excel

Puoi usare Excel DATEVALUE funzione per convertire un file Date per Serial Numberformato. È necessario allegare il fileDateargomento in "". Per esempio,

= DATEVALUE ("6/8/2016") restituisce 42529

Ottenere la data odierna

Se è necessario eseguire calcoli in base alla data odierna, utilizzare semplicemente la funzione Excel OGGI (). Il risultato riflette la data in cui viene utilizzato.

La seguente schermata di OGGI () uso della funzione che è stata presa il 16 ° maggio 2016 -

Trovare un giorno lavorativo dopo i giorni specificati

Potrebbe essere necessario eseguire determinati calcoli in base ai giorni lavorativi.

I giorni feriali escludono i giorni del fine settimana e le festività. Ciò significa che se puoi definire il fine settimana e le vacanze, qualunque calcolo tu faccia sarà basato sui giorni lavorativi. Ad esempio, puoi calcolare le date di scadenza delle fatture, i tempi di consegna previsti, la data della riunione successiva, ecc.

Puoi usare Excel WORKDAY e WORKDAY.INTL funzioni per tali operazioni.

S.No. Descrizione della funzione
1.

WORKDAY

Restituisce il numero di serie della data prima o dopo un numero di giorni lavorativi specificato
2.

WORKDAY.INTL

Restituisce il numero di serie della data prima o dopo un numero specificato di giorni lavorativi utilizzando i parametri per indicare quali e quanti giorni sono i giorni del fine settimana

Ad esempio, è possibile specificare il 15 ° giorno lavorativo dalla data odierna (screenshot qui sotto è preso il 16 ° maggio 2016), utilizzando le funzioni oggi e giornata lavorativa.

Supponiamo che il 25 ° maggio 2016 e il 1 ° giugno 2016 sono vacanze. Quindi, il tuo calcolo sarà il seguente:

Personalizzazione della definizione di un fine settimana

Per impostazione predefinita, il fine settimana è sabato e domenica, ovvero due giorni. Puoi anche opzionalmente definire il tuo weekend con ilWORKDAY.INTLfunzione. È possibile specificare il proprio fine settimana con un numero di fine settimana che corrisponde ai giorni del fine settimana come indicato nella tabella sottostante. Non è necessario ricordare questi numeri, perché quando inizi a digitare la funzione, ottieni un elenco di numeri e i giorni del fine settimana nell'elenco a discesa.

Giorni del fine settimana Numero del fine settimana
Sabato domenica 1 o omesso
Domenica lunedì 2
Lunedi martedì 3
Martedi mercoledì 4
Mercoledi giovedi 5
Giovedì venerdì 6
Venerdì sabato 7
Solo la domenica 11
Solo lunedì 12
Solo martedì 13
Solo mercoledì 14
Solo giovedì 15
Solo venerdì 16
Solo sabato 17

Supponiamo, se il fine settimana è solo venerdì, è necessario utilizzare il numero 16 nella funzione WORKDAY.INTL.

Numero di giorni lavorativi tra due date specificate

Potrebbe essere necessario calcolare il numero di giorni lavorativi tra due date, ad esempio, nel caso di calcolo del pagamento a un dipendente a contratto che viene pagato su base giornaliera.

Puoi trovare il numero di giorni lavorativi tra due date con le funzioni di Excel NETWORKDAYS e NETWORKDAYS.INTL. Proprio come nel caso di WORKDAYS e WORKDAYS.INTL, NETWORKDAYS e NETWORKDAYS.INTL consentono di specificare i giorni festivi e con NETWORKDAYS.INTL è inoltre possibile specificare il fine settimana.

S.No. Descrizione della funzione
1.

NETWORKDAYS

Restituisce il numero di giorni lavorativi interi tra due date
2.

NETWORKDAYS.INTL

Restituisce il numero di giorni lavorativi interi tra due date utilizzando i parametri per indicare quali e quanti giorni sono i giorni del fine settimana

È possibile calcolare il numero di giorni lavorativi tra oggi e un'altra data con le funzioni TODAY e NETWORKDAYS. Nella schermata indicato di seguito, oggi è il 16 ° maggio 2016 e la data finale è il 16 ° giugno 2016. 25 ° maggio 2016 e il 1 ° giugno 2016 sono vacanze.

Anche in questo caso, si presume che il fine settimana sia sabato e domenica. È possibile avere la propria definizione per il fine settimana e calcolare il numero di giorni lavorativi tra due date con la funzione NETWORKDAYS.INTL. Nella schermata riportata di seguito, solo il venerdì è definito come fine settimana.

Estrazione di anno, mese, giorno dalla data

È possibile estrarre da ciascuna data in un elenco di date, il giorno, il mese e l'anno corrispondenti utilizzando le funzioni di Excel GIORNO, MESE e ANNO.

Ad esempio, considera le seguenti date:

Da ciascuna di queste date, puoi estrarre giorno, mese e anno come segue:

Estrazione del giorno della settimana dalla data

È possibile estrarre da ogni data in un elenco di date, il giorno della settimana corrispondente con la funzione WEEKDAY di Excel.

Considera lo stesso esempio fornito sopra.

Ottenere la data da anno, mese e giorno

I dati potrebbero contenere separatamente le informazioni su anno, mese e giorno. È necessario ottenere la data combinando questi tre valori per eseguire qualsiasi calcolo. È possibile utilizzare la funzione DATA per ottenere i valori della data.

Considera i seguenti dati:

Utilizzare la funzione DATE per ottenere i valori DATE.

Calcolo di anni, mesi e giorni tra due date

Potrebbe essere necessario calcolare il tempo trascorso da una determinata data. Potresti aver bisogno di queste informazioni sotto forma di anni, mesi e giorni. Un semplice esempio potrebbe essere il calcolo dell'età attuale di una persona. È effettivamente la differenza tra la data di nascita e oggi. A questo scopo è possibile utilizzare le funzioni Excel DATEDIF, TODAY e CONCATENATE.

L'output è il seguente: