Formula generica
=IF(end>start, end-start, 1-start+end)
Sommario
Per calcolare il numero di ore tra due volte, puoi utilizzare una formula che sottrae l'ora di inizio dall'ora di fine. Se l'ora di inizio e di fine sono comprese tra mezzanotte, sarà necessario modificare la formula come spiegato di seguito. Nell'esempio mostrato, la formula in D5 è:
=IF(C5>B5,C5-B5,1-B5+C5)
Spiegazione
Per calcolare le ore tra le ore, quando entrambe le ore sono nello stesso giorno di calendario, puoi semplicemente sottrarre l'ora di inizio dall'ora di fine:
=end-start
E applica la formattazione dei numeri per visualizzare le ore.
Tuttavia, quando gli orari attraversano il confine di un giorno (mezzanotte), le cose possono diventare complicate. Leggi di seguito per vedere diversi modi per gestire questa sfida.
Come Excel tiene traccia del tempo
In Excel, un giorno è uguale a 1, che rappresenta 24 ore. Ciò significa che i tempi e le ore sono valori frazionari di 1, come mostrato nella tabella seguente:
| Ore | Tempo | Frazione | Valore |
|---|---|---|---|
| 3 | 3:00 DI MATTINA | 3/24 | 0.125 |
| 6 | 6:00 DI MATTINA | 6/24 | 0.25 |
| 4 | 4:00 DEL MATTINO | 4/24 | 0.167 |
| 8 | 8:00 DI MATTINA | 8/24 | 0.333 |
| 12 | 12:00 | 12/24 | 0,5 |
| 18 | 18:00 | 18/24 | 0.75 |
| 21 | 09:00 | 21/24 | 0.875 |
Calcolo semplice della durata
Quando l'ora di inizio e l'ora di fine sono nello stesso giorno, il calcolo della durata in ore è semplice. Ad esempio, con l'ora di inizio delle 9:00 e l'ora di fine delle 17:00, puoi semplicemente utilizzare questa formula:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Quando i tempi attraversano la mezzanotte
Il calcolo del tempo trascorso è più complicato se gli orari superano il limite di un giorno (mezzanotte). Ad esempio, se l' ora di inizio è alle 22:00 un giorno e l' ora di fine è alle 5:00 del giorno successivo, l'ora di fine è in realtà inferiore all'ora di inizio e la formula sopra restituirà un valore negativo ed Excel twill visualizzare una stringa di caratteri hash (########).
Per correggere questo problema, puoi utilizzare questa formula per gli orari che attraversano il confine di un giorno:
=1-start+end
Sottraendo l' ora di inizio da 1, ottieni la quantità di tempo nel primo giorno, che puoi semplicemente aggiungere alla quantità di tempo nel 2 ° giorno, che è uguale all'ora di fine .
Questa formula non funzionerà per volte nello stesso giorno, quindi possiamo generalizzare e combinare entrambe le formule all'interno di un'istruzione IF in questo modo:
=IF(end>start, end-start, 1-start+end)
Ora, quando entrambi gli orari sono nello stesso giorno, la fine è maggiore dell'ora di inizio , quindi viene utilizzata la formula semplice. Ma quando i tempi in un confine giorno viene utilizzata la seconda formula.
Alternativa alla funzione MOD
Usando la funzione MOD con un divisore di 1, possiamo semplificare la formula sopra a questo:
=MOD(end-start,1)
Qui la funzione MOD si prende cura del problema negativo utilizzando la funzione MOD per "ribaltare" i valori negativi al valore positivo richiesto. Questa versione della formula gestirà entrambi i casi, quindi possiamo eliminare l'istruzione IF condizionale.
Nota: nessuna delle formule precedenti gestirà durate superiori a 24 ore. Se ne hai bisogno, vedi l'opzione data + ora di seguito.
Per ulteriori informazioni sul modulo, ecco un buon collegamento su Khan Academy.
Formattazione delle durate di tempo
Per impostazione predefinita, Excel può visualizzare l'ora, anche l'ora che rappresenta una durata, utilizzando AM / PM. Ad esempio, se si dispone di un tempo calcolato di 6 ore, Excel potrebbe visualizzarlo come 6:00 AM. Per rimuovere AM / PM, applica un formato numerico personalizzato come h: mm.
Nei casi in cui il tempo calcolato supera le 24 ore, potresti voler utilizzare un formato personalizzato come (h): mm. La sintassi delle parentesi quadre (h) indica a Excel di visualizzare le durate delle ore maggiori di 24 ore. Se non si utilizzano le parentesi, Excel semplicemente "rollover" quando la durata raggiunge le 24 ore (come un orologio).
Semplificare il problema con data + ora
Puoi semplicemente risolvere il problema del calcolo del tempo trascorso lavorando con valori che contengono sia la data che l'ora. Per inserire una data e un'ora insieme, utilizzare un unico spazio tra l'ora e la data: 9/1/2016 10:00 AM
Quindi puoi utilizzare una formula di base per calcolare il tempo trascorso:
=end-start
Nell'esempio seguente i valori di inizio e di fine contengono sia le date che le ore:
La formula è:
=C5-B5
Formattato con il formato numerico personalizzato (h): mm, per visualizzare le ore trascorse.
