From: Cindy on
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" <Cindy(a)discussions.microsoft.com> wrote in message
> news:885F3AA7-8027-4726-9C0B-8C543332F132(a)microsoft.com...
> >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
> >
> >
>
From: Cindy on
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(B1>A1,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
> >
> >
From: Cindy on
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(B1>A1,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
> >
> >
First  |  Prev  | 
Pages: 1 2
Prev: Sorting and COUNTIF
Next: Import XML