subtracting date/time

[Nathan]

Hi all,

I have two columns: one is the "date time" a task began and the other is the
"date time" the task ended. I want to measure the difference in hours.

For example, in A1 I have "6/28/2008 15:44" and in B1 I have "7/1/2008
16:51" These cell values are based on a function
[=IF(AND(ISNUMBER(F66),ISNUMBER(G66)),F66+G66,"")] that has brought together
separate date and time values from other columns.

To find the difference between these two cells, I tried simply subtracting
(which gave me another date-time value) and I tried the following formula
based on another spreadsheet I've used
[=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),B1-A1,"")] but this gives me the
differences between the two times, but doesn't account for the difference
between dates.

Any thoughts?

Thanks,
Nathan

 

[Fred Smith]

Simple subtracting the two numbers, as in =b1-a1 *does* work. It's your
format which is causing the problems.

You need to:
1. Use a format which includes the date as well as the time, or
2. Use a format of [hh]:mm which will display more than 24 hours.

Regards,
Fred.

 

[StumpedAgain]

=DATEDIF(A1,A2,"M")
returns the difference in dates in months.

"D" is days
"Y" is years
"YD" is days excluding years
"YM" is months excluding years
"MD" is days excluding months and years

 

[Rick Rothstein \(MVP - VB\)]

Try this formula...

=24*(B1-A1)

Rick

 

[Bob Phillips]

Just use =B1-A1 and format as [h]:mm

 

[Nathan]

Thanks Fred. This worked.

Simple subtracting the two numbers, as in =b1-a1 *does* work. It's your
format which is causing the problems.

You need to:
1. Use a format which includes the date as well as the time, or
2. Use a format of [hh]:mm which will display more than 24 hours.

Regards,
Fred.

Hi all,

I have two columns: one is the "date time" a task began and the other is
the
"date time" the task ended. I want to measure the difference in hours.

For example, in A1 I have "6/28/2008 15:44" and in B1 I have "7/1/2008
16:51" These cell values are based on a function
[=IF(AND(ISNUMBER(F66),ISNUMBER(G66)),F66+G66,"")] that has brought
together
separate date and time values from other columns.

To find the difference between these two cells, I tried simply subtracting
(which gave me another date-time value) and I tried the following formula
based on another spreadsheet I've used
[=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),B1-A1,"")] but this gives me the
differences between the two times, but doesn't account for the difference
between dates.

Any thoughts?

Thanks,
Nathan

 

[Navy Guy]

I am trying to make this calculation work using the response above. When I
enter '=a2-a1' into cell a3 the result returned is '#VALUE!' (a value used
in this formula is of the wrong data type).

Format used for cells a1 and a2 is 'm/d/yyyy hh:mm'. Format used in cell
a3 is [hh]:mm.

I can't see why this won't work. Any suggestions?

Just use =B1-A1 and format as [h]:mm

--
__________________________________
HTH

Bob

Hi all,

I have two columns: one is the "date time" a task began and the other is
the
"date time" the task ended. I want to measure the difference in hours.

For example, in A1 I have "6/28/2008 15:44" and in B1 I have "7/1/2008
16:51" These cell values are based on a function
[=IF(AND(ISNUMBER(F66),ISNUMBER(G66)),F66+G66,"")] that has brought
together
separate date and time values from other columns.

To find the difference between these two cells, I tried simply subtracting
(which gave me another date-time value) and I tried the following formula
based on another spreadsheet I've used
[=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),B1-A1,"")] but this gives me the
differences between the two times, but doesn't account for the difference
between dates.

Any thoughts?

Thanks,
Nathan

 

[Fred Smith]

Your problem is that either a1 or a2 (or both) is text, not numbers. They
may look like dates and times, but they are text.

The easy way to tell is to try reformatting the cell (to any other
numeric/date/time format). If it doesn't change, you've got text.

The easy way to fix it, is to simply re-enter the numbers.

Regards,
Fred.

I am trying to make this calculation work using the response above. When I
enter '=a2-a1' into cell a3 the result returned is '#VALUE!' (a value
used
in this formula is of the wrong data type).

Format used for cells a1 and a2 is 'm/d/yyyy hh:mm'. Format used in cell
a3 is [hh]:mm.

I can't see why this won't work. Any suggestions?

Just use =B1-A1 and format as [h]:mm

--
__________________________________
HTH

Bob

Hi all,

I have two columns: one is the "date time" a task began and the other
is
the
"date time" the task ended. I want to measure the difference in hours.

For example, in A1 I have "6/28/2008 15:44" and in B1 I have "7/1/2008
16:51" These cell values are based on a function
[=IF(AND(ISNUMBER(F66),ISNUMBER(G66)),F66+G66,"")] that has brought
together
separate date and time values from other columns.

To find the difference between these two cells, I tried simply
subtracting
(which gave me another date-time value) and I tried the following
formula
based on another spreadsheet I've used
[=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),B1-A1,"")] but this gives me the
differences between the two times, but doesn't account for the
difference
between dates.

Any thoughts?

Thanks,
Nathan

 

[Workbook]

Hi Rick,

How can the formula you advised be modified to exclude weekends? Also is it
possible to exclude holidays as well?

Ed

Try this formula...

=24*(B1-A1)

Rick

Hi all,

I have two columns: one is the "date time" a task began and the other is
the
"date time" the task ended. I want to measure the difference in hours.

For example, in A1 I have "6/28/2008 15:44" and in B1 I have "7/1/2008
16:51" These cell values are based on a function
[=IF(AND(ISNUMBER(F66),ISNUMBER(G66)),F66+G66,"")] that has brought
together
separate date and time values from other columns.

To find the difference between these two cells, I tried simply subtracting
(which gave me another date-time value) and I tried the following formula
based on another spreadsheet I've used
[=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),B1-A1,"")] but this gives me the
differences between the two times, but doesn't account for the difference
between dates.

Any thoughts?

Thanks,
Nathan