JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
If Function to indicate "1" if a Time falls within a Time.
Hello All
firstly thanks for looking to see if you can help me;
Problem:
I want to work out how many staff i have working per hour. so across the top i have the hour window, along the left i have staff names with what hours they will be working. as below:
[TABLE="width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06:00[/TD]
[TD]07:00[/TD]
[TD]08:00[/TD]
[TD]09:00[/TD]
[TD]10:00[/TD]
[TD]11:00[/TD]
[TD]12:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]15:00[/TD]
[TD]16:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Staff[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]07:00[/TD]
[TD]08:00[/TD]
[TD]09:00[/TD]
[TD]10:00[/TD]
[TD]11:00[/TD]
[TD]12:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]15:00[/TD]
[TD]16:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]steve[/TD]
[TD]06:00[/TD]
[TD]18:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bob[/TD]
[TD]09:00[/TD]
[TD]17:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]steph[/TD]
[TD]16:00[/TD]
[TD]02:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Rachel[/TD]
[TD]20:00[/TD]
[TD]06:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
currently i have found 2 formulas as follows (this is how it would show if this was in cell D3):
=If(AND(D$1>=$B3,D$2>=$B3,D$1<=$C3,D$2<=$C3),1,0)
=If(AND(D$1 < $c3,d$2 > b$3),1,0)
i believe i have copied them right above, but anyway they would work perfect if my staff only worked until no later 23:00
but unfortunately i have shift which cross over midnight to the next days shifts start again from 06am.
can anyone help me work out what need to change in order to make the formula calculate the same for any shift which crosses over midnight!
thanks in advance and if anything is unclear then please let me know and i will try to explain better
Did you check your times in the top 2 rows like I suggested?
Did you check your times in the top 2 rows like I suggested?
sorry didnt initial see about the times, and yes i have checked the times and that was the issue, although this has brough an new issue.
all the shifts seem to be selecting an option between the hours 23:00-00 now
one thing after another here, sorry to bother you? can you think of a reason why it would now place a 1 within hours of 23:00-00:00
[TABLE="width: 1210"]
Again mine works fine, did you check all of the times in the top 2 rows?
Think I found it! For the hour 23:00 - 00:00 enter 00:00 as 01/01/1900 00:00:00, only this one no others, then it should work.
