Still Stuck in TIME - Pivot table and time

Sorry to beat a dead horse, but still stuck with the 24 hour issue of time.  I attached a sheet to show wmy problem. When you go from 0700-0700, I get 0 for my answer. Plus on my pivot table, my grand totals equal Zero.


Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:

If there is ever ANY possibility that a shift will cross a day boundary, then you should always record the date and time for both the "in" and the "out".

In the short term, change your formula for Total Hours to:


and change the number format for that column to numeric with two decimal places.

Now, rebuild your PivotTable.

Of course, this will only accommodate shifts that span a single day boundary.  Any shifts spanning 2+ day boundaries will be incorrect.

bvanscoy678Author Commented:
Our shifts always cross two days. We start at 0700 and work until 0700 the next day.

I have been playing with this formula, but still not good.


Thank you
Go with matthews formula and you can change the number format in the PivotTable and sheet to [h]:mm:ss

-RK expert-exchange-v1.xlsx
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

bvanscoy678Author Commented:
Yes, using your formula, I do get same results. I am trying to simplfy the process, but might have to include date with time.

Time IN      Time OUT      "Total
7:00      15:47      18:48
15:47      18:00      5:12
18:00      7:00      0:00
7:00      7:00      0:00
Thats because of the number format. Regular time format is "hh:mm:ss" which goes around in 24 hours. now [h]:mm:ss sums the hours even over 24...
bvanscoy678Author Commented:
Yes, thank you. It was the formatt I was messing up.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.