is this the best way to use the IF statement with time?

[mikester]

Basically I'm trying to create a formula that works out from the time in a cell if a driver should be paid out of hours or not. OOH runs from 1800 to 0759, so if the cell shows a time equal or in between these times the cell containing the formula should read "Yes", and viceversa in the other circumstance.


This is where I'm at right now:

=SUM(IF(I8>TIME(8,0,0),"No"),IF(I8<=TIME(17,59,0),"No"),IF(I8>=TIME(18,0,0),"Yes"),IF(I8<=TIME(7,59,0),"Yes"))

Unfortunately I get the #value! error, I thought this meant there's text and numbers mixed up.. however I cannot for the life of me get this formula to work. Any ideas?

Also, this formula is the basis for my end goal, I also want to include weekends and bank holidays in this. How can I go about achieving this? Would I need to incorporate the next few years calender including bank holidays on a separate sheet and what would the complete formula look like?

It's a big ask I know, but if i can at least get the Yes/No to work on the time I can then work on the days of the week.


I look forward to your replies.

 

[Ruddles]

I think your IF functions are individually producing Yeses and Noes, and then you're trying to SUM them. You can't add Yeses and Noes together - you can count them but you can't add them.

Try:-

=IF(OR( [COLOR=blue]I8>=TIME(18,0,0) [/COLOR], [COLOR=magenta]I8< TIME(8,0,0) [/COLOR]),"Yes","No")

 

[mikester]

I think your IF functions are individually producing Yeses and Noes, and then you're trying to SUM them. You can't add Yeses and Noes together - you can count them but you can't add them.

Try:-

=IF(OR( [COLOR=blue]I8>=TIME(18,0,0) [/COLOR], [COLOR=magenta]I8< TIME(8,0,0) [/COLOR]),"Yes","No")

that is excellent, works like a charm.

I tried something similar but with the 'AND' statement, thought it was the right way but it didn't work:

=IF(AND(I3>TIME(18,1,0),I3<TIME(7,59,0)),"Yes","No")





do you know how i could also include the weekends into this formula?

 

[Ruddles]

=IF(AND(I3>TIME(18,1,0),I3<TIME(7,59,0)),"YES","NO")< b>

You have to insert a space after a < character otherwise the forum software thinks you're sending it some HTML and it gets its knickers in a twist.

Try again with a space after the < and explain what you want the formula to do.

do you know how i could also include the weekends into this formula?

I think it's probably something to do with the NETWORKDAYS function but I've never used it - I've never needed to.

 

[mikester]

OK Ruddles, thanks for your help!!

 

[barry houdini]

do you know how i could also include the weekends into this formula?

Hello mikester,

Can you give an example of what you want to do - if OOH runs from 18:00 to 07:59 then where do weekends and holidays come in....are all hours on those days OOH?

How are you showing the data - do you have a separate cell with the date and then a start and end time.....or some other setup?

 

[mikester]

Hi Barry,

Okay so essentially I can pull a report out of our transport software, it contains the times when jobs come out. The time is listed starting in cell I3 and the date created listed starting in I3.

Weekends are classed differently, so after 1800 Friday all the way to Monday 0759 is weekend callout. So this would mean there are 3 states that need to be displayed, "no-callout", "call-out" and "weekend call-out".

The report is a daily report so the dates will all be the same but if the report was to run over a week i want to be able to see what type of callout applies so we can work out if our rider pay is correct.

this is what it looks like at the moment:

obviously the "is callout required" now needs to be "type of callout required", but i'm not sure how to incorporate working days and even UK bank holidays in there...

If you think you can help Barry I'd be greatly receptive to your ideas..

 

[barry houdini]

OK, forgetting about holidays for a moment, try this formula

=IF(COUNTA(A2,I2)=2,IF((WEEKDAY(A2,2)<6)*(I2>=1/3)*(I2<0.75),"No ",IF(WEEKDAY(A2+I2-1/3,2)+I2>5.75,"Weekend ",""))&"call-out","")

If you want to factor in holidays.....what do they count as - is that another category?