Subtract 12 hours from date/time

[Cindy]

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

 

[ozgrid.com]

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

 

[FSt1]

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(B1>A1,A1-0.5,0)

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

regards
FSt1

 

[Pritesh]

Hi,

See if it works this way;

=IF(B1>A1,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]

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

 

[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

 

[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