I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

Hi Cindy,

To subtract 12 hours from a date/time cell, you can use the following formula:

1. =IF(B1A1,B1-TIME(12,0,0),"")

Here's how it works:

1. The IF function checks if the date in B1 is greater than the date in A1. If it is, the formula subtracts 12 hours from the date/time in B1. If it's not, the formula returns an empty string ("").

2. The TIME function creates a time value of 12:00:00 AM (i.e. 12 hours) that can be subtracted from the date/time in B1.

3. The result is displayed in cell C1.

Make sure that the format of cell C1 is set to date/time format so that the result is displayed correctly.

Valid date & times would like: 3/3/2010 20:30 else your date and times are
text.



--
Regards
Dave Hawley
www.ozgrid.com
"Cindy" wrote in message
...
I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

hi
would have been nice if you had posted your formula so someone might see
what your are doing wrong.

excel keeps time as a decimal value of a day.
12 hrs = .5 day

assuming that your date/time that you are subtracting is A....
=IF(B1A1,A1-0.5,0)

post back with more details. maybe we can refine it more.

regards
FSt1


"Cindy" wrote:

I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

Hi,

See if it works this way;

=IF(B1A1,A1-time(8,0,0))

If still you get error, your data might be errorneous. Then your first step
should be to fix data-error. Please post your feedback or progress.

Regards,
Pritesh


"Cindy" wrote:

I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

Hi,

See if it works this way; (I assumed you want to reduce 8 hours from B1).

=IF(B1A1,B1-time(8,0,0),B1)

If still you get error, your data might be errorneous. Then your first step
should be to fix data-error. Please post your feedback or progress.

Regards,
Pritesh

--
Regards,
Pritesh


"Cindy" wrote:

I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

Hmm, you are right. Once I changed the time to 20:30 it worked. My old
military mind doesn't see time with a colon in it. LOL

Thanks!

Cindy

"ozgrid.com" wrote:

Valid date & times would like: 3/3/2010 20:30 else your date and times are
text.



--
Regards
Dave Hawley
www.ozgrid.com
"Cindy" wrote in message
...
I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

Actually my formula was the same as yours, but I had the time in A1 and B1
formatted incorrectly. I was putting in 2030 (true military time) instead of
20:30 which is what Excel wants.

Thanks for your help though!

Cindy

"FSt1" wrote:

hi
would have been nice if you had posted your formula so someone might see
what your are doing wrong.

excel keeps time as a decimal value of a day.
12 hrs = .5 day

assuming that your date/time that you are subtracting is A....
=IF(B1A1,A1-0.5,0)

post back with more details. maybe we can refine it more.

regards
FSt1


"Cindy" wrote:

I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy

This also worked but you hit the nail on the head with the erroneous data. I
was putting in 2030 instead of 20:30.

Thanks for the alternative formula, I've put it in my notebook for future
knowledge!

Cindy

"Pritesh" wrote:

Hi,

See if it works this way; (I assumed you want to reduce 8 hours from B1).

=IF(B1A1,B1-time(8,0,0),B1)

If still you get error, your data might be errorneous. Then your first step
should be to fix data-error. Please post your feedback or progress.

Regards,
Pritesh

--
Regards,
Pritesh


"Cindy" wrote:

I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.

A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)

No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
appreciated.

Cindy