Subtract 12 hours from date/time
 

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

Answer: Subtract 12 hours from date/time
 

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.

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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

Subtract 12 hours from date/time
 

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