Solved

Still Stuck in TIME - Pivot table and time

Posted on 2011-02-25
6
211 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bvanscoy678
  • 3
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34982723
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
 

Author Comment

by:bvanscoy678
ID: 34982830
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
 
LVL 5

Expert Comment

by:roger_karam
ID: 34982845
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bvanscoy678
ID: 34982847
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
 
LVL 5

Expert Comment

by:roger_karam
ID: 34982862
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
 

Author Closing Comment

by:bvanscoy678
ID: 34982908
Yes, thank you. It was the formatt I was messing up.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question