lavorare con il tempo trascorso in Excel 2010

Ho un foglio di calcolo Excel in cui i tempi dei dipendenti sono stati registrati con text e numbers, come in 7 ore e 6 minuti. Devo convertire i dati esistenti in h: mm in modo da poter automaticamente dedurre le pause pranzo. Qualsiasi consiglio apprezzato!

Consulenza standard per problemi come questo non è fare molto in una sola formula (in una sola cella), ma piuttosto per diffondere il lavoro su più celle. Tipicamente gli input ei risultati saranno in colonne parallele; quindi l'approccio consigliato è quello di calcolare i risultati intermedi in altre colonne, comunemente chiamate "colonne di helper". Una volta che hai tutto funzionato, puoi hide le colonne dell'assistente e / o puoi metterle fuori dalla vista posizionandole a destra dei dati principali (ad esempio colonne AAAG ).

Ecco qualcosa che ho gettato insieme:

 A1: 7 ore 6 minuti
 B1: = TROVA ("ore", A1)
 C1: = TROVA ("ora", A1)
 D1: = TROVA ("minuto", A1)
 E1: = VALUE (SINISTRA (A1, C1-1))
 F1 = VALUE (IF (ISERROR (B1), MID (A1, C1 + 4, D7- (C1 + 4)), MID (A1, B1 + 5, D1- (B1 + 5)))
 G1: = AND (F1> = 0, F1 <60)
 H1: = IF (OR (ISERROR (C1), ISERRORE (D1), ISERRORE (E1), ISERRORE (F1), ISERRORE (G1)), TRUE, NON (G1)
 I1: = IF (H1, "Invalid", TIME (E1, F1, 0))

La colonna I contiene il risultato desiderato. (Probabilmente lo sapete già, ma se viene visualizzato come numero, è una frazione di un giorno. Puoi farlo visualizzare come hh : mm formattandola.) Alcune spiegazioni:

  • Cerca le corde che ci aspettiamo di vedere. Cerca "ora" e "minuti" per consentire l'immissione di "1 ora 30 minuti" o "5 ore 1 minuto".
  • Estrarre (con LEFT e MID ) i pezzi della string di input che ci aspettiamo di contenere i numbers. Valutarli (trasformateli in numbers che possiamo fare con aritmetica) chiamando VALUE .
  • Se c'è un errore, visualizza "Invalid", altrimenti calcolare la durata del tempo dal numero di ore e dal numero di minuti.

Gli appunti:

  • È flessibile (apatico) sufficiente per accettare "1 ora 30 minuti" o "5 ore 1 minuti".
  • Segnala "Invalid" se il numero di minuti è <0 o ≥ 60, ma butterà un #NUM! errore se il numero di ore è <0 e, se è ≥ 24, la tratta module 24 (perché è ciò che fa la function TIME ).
  • Il text non numbersco in cui i numbers dovrebbero essere ("FOO ore minuti di bar") provocherà un errore, ma i numbers non interi non verranno trattati come "7 ore 0 minuti" ("7 ore 0 minuti" TIME function TIME fa).
  • Segnala "Invalid" se non trova le stringhe "ora" e "minuti", quindi "7 ore" causeranno un errore (usa "7 ore 0 minuti"), ma ignora il text estraneo dopo la parola " minuti ", così" 7 ore 6 minuti fino a quando non posso tornare a casa "è OK.

Spero che questo sia sufficiente per adattarlo alle tue esigenze.