I've used the formula =COUNTIFS(Schedule!$AC$6:$AC$96,"<"&AE$34,Schedule!$AD$6:$AD$96,">"&AD$34) for all other hours, but for a shift that spans across midnight (i.e. 11:00PM-12:00AM, 12:00AM-1:00AM, 1:00AM-2:00AM, etc.), my formula doesn't work! My AC6:AC96 are shift start times, AD6:AD96 are shift end times, AD34 and AE34 are my paramaters (11:00 PM and 12:00 AM, respectively). I am using the custom format h:mm AM/PM for all my times. Can someone please help ASAP?
Hi,
For such situation I have used a helper column that contains something that indicates whether the shift end time is the next day.
If you have columns that provide date data then you can combine the date and time so that the span across the midnight can be calculated.
If you provide a (sanitized) working copy of your spreadsheet, I can help provide a more effective recommendation.
Cheers
I was trying to do it without the date, but I do have the date above it- not sure if that will help, though! Here you go! I put a sample shift in the Schedule sheet in AC31:AD31, and the formulas in AF57:AO57 in the Hourly Balance sheet are not picking them up. Thanks!
Try
hours between any 2 times
=MOD(AD31-AC31,1)
@JohnTopley - Thanks, but I don't think that will work. I need to know how many staff are on shift between these hours:
1- 11 p.m. and midnight
2- midnight and 1 a.m.
3- 1 a.m. and 2 a.m.
4- 2 a.m. and 3 a.m.
5- 3 a.m. and 4 a.m.
My formula works for all hours up to 11 p.m.
If your Projected Labor will always be less than your Scheduled Labor, you might be able to use:
in AF7 ?
Please Login or Register to view this content.
Hi, and welcome to the forum
For situations like this, which are quite common with these time sheet type calculations, I generally add an IF Test
your_formula+IF(end_time_cell is less than start_time_cell,24,0)
..once more sucuri is rejecting the If formula when the less than symbol is included
Last edited by Richard Buttrey; 12-09-2016 at 04:15 PM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
@Logit- Thanks, but I actually need to put the formula into AF6... I'm trying to figure out which formula to use in A6 that will tell me how many employees from my "Schedule" tab are working between 11pm and midnight on that day.
@Richard- Thanks, but I'm not sure what IF statement to use in this case... I tried another one I found, but it didn't seem to work.
Paste the following formula into D6 and copy across:Let us know if you have any questions.Formula:
Please Login or Register to view this content.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
@JeteMc- Thank you SO much!!!
You're So Welcome. Thank You for the feedback and for taking the time to mark the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks