Link to home
Create AccountLog in
Avatar of shland
shland

asked on

calculate duration date time in excel and pivot table.

Hi,

I have a excel sheet contains information about ticket status from open to close as shown below. what is the formula to calculate the duration between date and time in this format in excel.?

eg:

A                              B                                C                                 D
DEFECT_ID      STATUS_NEW      STATUS_CLOSED            DURATION
1                 8/24/2012 10:34      8/31/2012 10:50                ?


2) Also can you show how we can do this through pivot table to calcuate the duration.

thanks,
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of spattewar
spattewar

Hi,

Just to add one more formatting to the column here. If you want to get the elapsed number of hours then use the format [h]:mm instead of [hh]:mm as [hh]:mm will not consider the date difference.

hope it helps.
Not sure what you mean...

with the OP's dates and my suggested format of [hh]:mm, i get 168:16 as it does with [h]:mm

the only difference I see is when less than 24 hour difference, the [hh] give 2 digit hours (i.e 00 instead of 0.. so it would be a preference issue.
I guess NB_VC you are correct. I never tried this but had read in an article that this is what it does if there is no date, and only time entries.
So both of them are same is confirmed. Thanks.