Subtract 12 hours from date/time

  • Thread starter Thread starter Cindy
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top