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,

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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.

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.

So both of them are same is confirmed. Thanks.

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.