Determine elapsed time between two dates

[Guest]

I have a spreadsheet where I need to calculate the raw amount of time between
when a problem was called in and when it was resolved. I am importing data,
which is a date-time group.

for instance, I need to subtract 10-1-04 17:55 from 10-2-04 18:30. In this
instance, the answer should be 24:35, as in 24 hours and 35 minutes. It
seems whatever I try with formats or anything else is giving me a date-time
group instead of a pure elapsed time.

After I do this, I need to compare it to a standard to evaluate our service
levels.

Should I have the standard in the same format ? [h]:mm:ss, or will this
affect
anything?

I'm currently doing this in VBA with the following line:

Cells(n, 15).Value = Cells(n, 10).Value - Cells(n, 3).Value

where the cells in question contain the date-time values.

Thanks

 

[Tom Ogilvy]

Cells(n, 15).Value = cDate(Cells(n, 10).Value) - cDate(Cells(n, 3).Value)
Cells(n,15).Numberformat = "[h]:mm:ss"

for the comparison cell, it doesn't make any difference, but it will be more
intuitive if you do

 

[Guest]

Thanks Tom.

It works great, and I'm all for intuitive.

Cells(n, 15).Value = cDate(Cells(n, 10).Value) - cDate(Cells(n, 3).Value)
Cells(n,15).Numberformat = "[h]:mm:ss"

for the comparison cell, it doesn't make any difference, but it will be more
intuitive if you do

--
Regards,
Tom Ogilvy

I have a spreadsheet where I need to calculate the raw amount of time between
when a problem was called in and when it was resolved. I am importing data,
which is a date-time group.

for instance, I need to subtract 10-1-04 17:55 from 10-2-04 18:30. In this
instance, the answer should be 24:35, as in 24 hours and 35 minutes. It
seems whatever I try with formats or anything else is giving me a date-time
group instead of a pure elapsed time.

After I do this, I need to compare it to a standard to evaluate our service
levels.

Should I have the standard in the same format ? [h]:mm:ss, or will this
affect
anything?

I'm currently doing this in VBA with the following line:

Cells(n, 15).Value = Cells(n, 10).Value - Cells(n, 3).Value

where the cells in question contain the date-time values.

Thanks

 

[Guest]

I don't know anything about VBA and I tried to put the code you reference in
my worksheet - no luck of course. I need to know the days between two date
cells. Is there a simple formula like there is for cells with numbers?

Thanks
John

 

[Myrna Larson]

Dates and times ARE numbers: specifically the number of whole and partical
days since Dec 31, 1899.

Excel has no "elapsed time" data type.

Assuming the cell contains both the date and time, you can simply subtract the
start time from the end time. Format the cell as [hh]:mm, or select from the
list of Time formats the one that shows hours > 24.

I don't know anything about VBA and I tried to put the code you reference in
my worksheet - no luck of course. I need to know the days between two date
cells. Is there a simple formula like there is for cells with numbers?

Thanks
John

I have a spreadsheet where I need to calculate the raw amount of time between
when a problem was called in and when it was resolved. I am importing data,
which is a date-time group.

for instance, I need to subtract 10-1-04 17:55 from 10-2-04 18:30. In this
instance, the answer should be 24:35, as in 24 hours and 35 minutes. It
seems whatever I try with formats or anything else is giving me a date-time
group instead of a pure elapsed time.

After I do this, I need to compare it to a standard to evaluate our service
levels.

Should I have the standard in the same format ? [h]:mm:ss, or will this
affect
anything?

I'm currently doing this in VBA with the following line:

Cells(n, 15).Value = Cells(n, 10).Value - Cells(n, 3).Value

where the cells in question contain the date-time values.

Thanks

 

[Tom Ogilvy]

=Trunc(EndDate-StartDate)

=Trunc(A2-A1)

format the cell as General (it will probably default to a date format - so
the results will look screwy until you format it to general).

--
Regards,
Tom Ogilvy

I don't know anything about VBA and I tried to put the code you reference in
my worksheet - no luck of course. I need to know the days between two date
cells. Is there a simple formula like there is for cells with numbers?

Thanks
John

I have a spreadsheet where I need to calculate the raw amount of time between
when a problem was called in and when it was resolved. I am importing data,
which is a date-time group.

for instance, I need to subtract 10-1-04 17:55 from 10-2-04 18:30. In this
instance, the answer should be 24:35, as in 24 hours and 35 minutes. It
seems whatever I try with formats or anything else is giving me a date-time
group instead of a pure elapsed time.

After I do this, I need to compare it to a standard to evaluate our service
levels.

Should I have the standard in the same format ? [h]:mm:ss, or will this
affect
anything?

I'm currently doing this in VBA with the following line:

Cells(n, 15).Value = Cells(n, 10).Value - Cells(n, 3).Value

where the cells in question contain the date-time values.

Thanks

 

[Jako]

Sorry to to jump on this subject, but i have a query along the same
lines.

It concerns the "WEEKDAY" function.

I know very little is documented of it anyway, but is there an
equivalent VBA routine that can be used.

Assuming earlier date is in Cell "A1" and later date is in Cell "B1"
and the resulting difference (in days) is in Cell "C1".

Please advise if possible to do in VBA or not.

Many thanks

 

[Chip Pearson]

Jako,
To determine the number of days between two dates, simply
subtract one date from the other. E.g.,

Dim Dif As Long
Dif = Range("B1").Value - Range("A1").Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jako]

Many Thanks for that info Chip.

Didn't realise it would be that simple!!!

Easier than the standard function