Forum Discussion

Copper Contributor

Apr 17, 2020

Time formula in Excel

I'm using a time formula to calculate 12 am to 12 am between two days as 24:00 in an employee time log sheet. =IF(C15>B15,C15-B15,C15+1-B15) I also have the formula total each daily column into a ...

Example of Excel time formula.docx63 KB

excel

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Apr 18, 2020

willis71 Change to:

=IF(OR(B15="",C15=""),"",IF(C15>B15,C15-B15,C15+1-B15))

It determines first that both times are filled in. If not, the (blank) otherwise calculate the duration. The reason for 24:00 to show when no times are entered is the "C15+1" part in your formula. Two blank cells will always trigger the last argument, being "(blank)+1-(blank)". This equals 1 and when formatted as time, it represents 1 day = 24 hours.