Forum Discussion

willis71's avatar
willis71
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 weekly total.

When I remove the two 12am values, the total is still displaying 24:00 as if the values are still there. How can I have this display a zero when the original is removed? I've included screen shots of excel. Any help would be appreciated. 

excel
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 18, 2020

    willis71 

    As variant

    =MOD(C10-B10,1)

    displays overnight and one day hours as well as zero if both times are missed

  • Riny_van_Eekelen's avatar
    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.

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Apr 18, 2020

    Hiwillis71;

     

    please attached the sample excel file to assist you on your query, you can remove that 24:00 by using IF Function.. 

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

Resources