Can somebody explain this

[Fegal]

Iam working on a formula that says
If J48 = Y and P10 is greater or less that 80:00:00 then true.
So i can then condition format it.

I imagine it will be something like
=AND(J48="Y")(P10)<>TIME(80:00:00)
Not really sure, just trying stuff to see what works

What is confusing me is that when I type P10<TIME(80,00,00) It says FALSE..
If i type P10>TIME(80,00,00) It says TRUE.

P10 is 80:00 with the cell format [H]:mm which in general text is 3.33333333.
How do i get around this.

Regards
James

 

[slinky]

How are you arriving at 80:00:00... Perhaps you could amend the method through which you arrive at that to deliver a time serial based value as opposed to the displayed value..

80 hours is 3.333 days, or 3.33333333 in time serial speak.
80:00:00 is seen by Excel as 0800 on 03/01/1900 (3 days and 8 hours from 00:00 01/01/1900)

 

[Fegal]

80 is the normal work fortnight. So I get there by subtracting the start time from the finish time, This time changes depending on shift.

How would you recommend I show 10, 8 hour shifts?

the formula will be I think =AND($J$48="Y",P10<>TIME(80,00,00))
But that isnt working because of this time issue.

Regards James

 

[Dave Patton]

You challenge is not clear to me.

You can consider

=(J48="Y")*(P10<>"80:00:00"+0) which will yield 1 for True or 0 for False

or

=AND(J48="Y",P10<>"80:00:00"+0)