Subtract 12 hours from date/time

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
 
O

ozgrid.com

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

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
 
P

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
 
C

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
 
C

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top