This is a follow up to another thread of same name.
I was not able to get the formula to work as previously described, but perhaps I am not looking at it correctly. I have attached a sample workbook of what I am trying to accomplish and would appreciate some tweaking of the formula.=(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)=2)+0
so If a1 = 18:00 and B1 = 02:30 then 19:00 or 01:00 in BR4 will give a 1 but 03:00 will give a zero, is that what you need?
Of course the formula still works if your shift doesn't pass through midnight....
The formula works for shifts between 0000 and 2400 but does not when crossing over midnight.
I would also like to understand the logic behind the formula. I know it has to do with T/F conditions, but I'm confused about the "2" in the formula.
Thanks,
TC
Last edited by tcowen; 10-29-2009 at 05:45 PM.
try this in D2: =IF(B2>C2,C2+1-B2,C2-B2) and drag down
sorry format your times military( the 13:30 option) as well as your D column
Last edited by khamilton; 10-29-2009 at 04:20 PM. Reason: missed parenthsis
To get the existing formulas in column F, G, H etc.. to work correctly across midnight, enter the stoptimes in column C as times, not as formula (starttime plus hrs), see attachment.
Last edited by WHER; 10-29-2009 at 04:33 PM.
see the attached.
modytrane
example attached to my prv msg works when column C has formulas.
modytrane
Thanks to you all. Works great. Now I just have to figure out which would be best to use in my application with least amount of input from end user.
Thanks again.
Can one of you explain how this is working. I realize it has to do with the T/F function of Excel but I really want to understand why this works so I can use it in future applications.
Here's what I understand:
StartTime <= SchdHr T=1, F=0
StopTime > SchdHr T=1, F=0
StartTime > StopTime T=1, F=0
what does the =2)+0 do?
As I said, I'm curious to know why this works?
The formula goes: (condition1 + condition2 + condition3) = 2, meaning two out of 3 conditions have to be true (1) to give a sum of 2 >> giving (2) = 2, this in turn results in true.
Then finally zero is added to this true, resulting in 1 as final result, the same result can be obtained with: true-0, true*1, true/1
Thank you very much!
Hey, That formula works thanks but I was just wondering if you new what formula I could use to make it not count the first 8 hours as of the first 8 hours being normal time and the rest overtime.Originally Posted by khamilton
Thanks for the help
Lee Glide
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
Audere est Facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks