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

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


"JonR" wrote in message
...
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

Thanks Tom.

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

"Tom Ogilvy" wrote:

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


"JonR" wrote in message
...
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

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


"JonR" wrote:

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

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.


On Thu, 21 Oct 2004 11:11:02 -0700, jch wrote:

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


"JonR" wrote:

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

=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


"jch" wrote in message
...
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


"JonR" wrote:

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

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


--
Jako
------------------------------------------------------------------------
Jako's Profile: http://www.excelforum.com/member.php...fo&userid=8710
View this thread: http://www.excelforum.com/showthread...hreadid=266913

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" wrote in message
...

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


--
Jako
------------------------------------------------------------------------
Jako's Profile:
http://www.excelforum.com/member.php...fo&userid=8710
View this thread:
http://www.excelforum.com/showthread...hreadid=266913

Many Thanks for that info Chip.

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

Easier than the standard function

--
Jak
-----------------------------------------------------------------------
Jako's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread.php?threadid=26691