Hey guys, i've got a pivot table which spits out the following.
So eg Cell B1 will equal 15/11/2016 6:50:28AM and cell C1 will equal 15/11/2016 3:28:12PM. I need a formula i can use or use in a calculated field to get the difference between the 2 cells in hours. So eg 8 hours 38 min or whatever it is.
I tried B1 - C1 which came up with 0.35 which i can't get to equate to a time period.
Cheers
0.35 is representing roughly a third of a day, that is, roughly 8 hours. Format as hh:mm:ss.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
What you need to understand about dates and times in excel is...
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed Feb 2017) is actually 42781
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
sounds like your answer is correct, as Trevor suggested, just format it as needed
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Next question is how do i do that in a calculated field. B1 is Min of Time and B2 is Max of time. I tried putting into the formula bar in the calculated field ='Min of Time'-'Max of Time' but it came up with an error.
Bump would be really appreciated
Nothing to work with. Post a sample workbook.
If your formula C1-B1 gives you 0.35, then right click on this cell (0.35) and select "Format Cells". Then select custom and type in either hh:mm:ss (as suggested above) or [h]:mm to allow for amounts over 24 hours.
If you want to subtract the min time from the max time in a range, maybe something like...
=max(A1:A10)-min(A1:A10)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks