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.

Thanks

expert-exchange.xlsx
bvanscoy678Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
bvanscoy678,

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:

=24*(G6+(F6>=G6)-F6)

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.

Patrick
0
 
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.

=IF(E6=F6,TIME(23,59,59)+TIME(0,0,1);F6+(E6>F6)-E6)


Thank you
0
 
roger_karamCommented:
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
0
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
Hours"
7:00      15:47      18:48
15:47      18:00      5:12
18:00      7:00      0:00
7:00      7:00      0:00
0
 
roger_karamCommented:
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...
0
 
bvanscoy678Author Commented:
Yes, thank you. It was the formatt I was messing up.
0
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.