# Can somebody explain this

#### Fegal

##### Board Regular
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)

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

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)

Replies
0
Views
550
Replies
10
Views
339
Replies
3
Views
256
Replies
19
Views
457
Replies
5
Views
325

1,202,962
Messages
6,052,821
Members
444,602
Latest member
Cookaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back