Share via

How to calculate hours worked when end time is after midnight

Anonymous
2012-02-07T18:48:10+00:00

I am trying to calculate time worked in a day. 

In one cell I enter the "start time", i.e. 4:00 pm

In the next cell I enter the "end time", i.e. 1:00 am

In the third cell I enter this formula, =INT((C163-B163)*24). 

This works great and gives me the hours worked in a day until I enter a value after midnight for the end time.  Then it returns a negative value.

How can I figure out the time worked when the employee works past 12:00 am?

Any help would be appreciated.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-02-07T19:07:29+00:00

    Hi,

    It would be easier if the date is declared, but that's only a option. Try this...

    for Time Format: =IF(C163<B163,((C163-B163))+1,C163-B163)

    for Decimal Format: =IF(C163<B163,((C163-B163)*24)+24,(C163-B163)*24)

    Hope it helps,

    ~jaeson

    20+ people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-02-07T19:57:16+00:00

    Hi,

    Try these

    Format as general to get the time in decimal

    =(C163-B163+(C163<B163))*24

    or format as time

    =(C163-B163+(C163<B163))

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-03-23T18:42:26+00:00
    0:00 5:00 7:37 7:32 7:29 10:01 0:00 20:09

    It worked until I had calculations of work hours past 12AM. It would add the first four numbers but when I added in the 7:29, the total of 20:09 decreases instead of increasing.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-02-07T20:00:40+00:00

    That worked great, although the first formula returned a decimal and the second formula returned an integer for the hours worked.  I used the second formula and got the result I was looking for.  Thanks for your help!

    0 comments