subtracting date/time
 

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

subtracting date/time
 

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.

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

subtracting date/time
 

=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

--
-SA


"Nathan" wrote:

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

subtracting date/time
 

Try this formula...

=24*(B1-A1)

Rick


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

subtracting date/time
 

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

--
__________________________________
HTH

Bob

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

subtracting date/time
 

Thanks Fred. This worked.

"Fred Smith" wrote:

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.

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

subtracting date/time
 

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?



"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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

subtracting date/time
 

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.

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



"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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

subtracting date/time
 

Hi Rick,

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

Ed

"Rick Rothstein (MVP - VB)" wrote:

Try this formula...

=24*(B1-A1)

Rick


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