Date Formula Query (I'm stuck!)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rcurtis8@gmail.com - 16 Aug 2007 11:17 GMT

Thanks for looking at my post :)

I am stuck on an 'IF' formula. I am trying to make the formula
recognise if a cell is more than or equal to 10 hours. See cell
details below:

cell D41: 01/01/1900 10:10:00

cell F41: =IF(D41>="01/01/1900 10:00:00","TRUE","false")

Upon clicking 'Evaluate' (Tools / Formula Auditing / Evaluate
Formula), I get the following evaluation:

IF(1.42361111111111>="01/01/1099 10:00:00","TRUE,"false")

It would appear that the format in cell D41 is not recognised by the
format in cell F41.

Any ideas where I am going wrong?

Cheers, Rob

Reply to this Message

aidan.heritage@virgin.net - 16 Aug 2007 11:30 GMT

On 16 Aug, 11:17, rcurt...@gmail.com wrote:

> Thanks for looking at my post :)
>
[quoted text clipped - 17 lines]
>
> Cheers, Rob

Your formula tests a number (1.4236...) against a TEXT value - you
could use datevalue to convert that, but why make life so difficult

10 hours is 10/24 so

IF(D41>=10/24,"TRUE","false")

Oh, and if you REALLY want true and false to be returned

=D41>=10/24

will do that quite nicely on it's own!

Reply to this Message

rcurtis8@gmail.com - 16 Aug 2007 11:47 GMT

On 16 Aug, 11:30, "aidan.herit...@virgin.net"
<aidan.herit...@virgin.net> wrote:

> On 16 Aug, 11:17, rcurt...@gmail.com wrote:
>
[quoted text clipped - 34 lines]
>
> - Show quoted text -

Hi Aidan,

Thanks a lot for the response. I made the change and am now getting a
true response. However, when I change D41 to 2 hours, I still get a
true response when onviously this should change to false.

Any ideas?

Cheers, Rob

Reply to this Message

Bob Phillips - 16 Aug 2007 12:06 GMT

I don't, but I would use this simpler formula

=D41>=TIME(10,0,0)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> On 16 Aug, 11:30, "aidan.herit...@virgin.net"
> <aidan.herit...@virgin.net> wrote:
[quoted text clipped - 46 lines]
>
> Cheers, Rob

Reply to this Message

Rate this thread: