Calculate total hours scheduled each hours of the day

**Trippi** · 04-27-2021, 04:35 PM

Hi All - somewhat of a newbie and I get most of my learnings reading forums to solve complex (to me) problems. I have been searching and cannot find a solution and most that I find or much more advanced than I am.

Based on a set of employee schedules, I have to figure out how many total hours are scheduled each hour. The shifts may cross midnight and they may not. The date is not relevant for this. I can figure out how to count number of employees in each hour of the day, but I would like to know how many 'hours' fall into each hour of the day.

Row 1 = hours of the day
Column A = Start Time
Column B = Stop Time

10:30p - 7a
5:00a - 1:30pm

10Pm hour should give me 30 minutes
11pm hour should give me 60 minutes
6am hour should give me 120 minutes

ETC...

I think I have a sourced a formula that will work but it only works for times that do not cross midnight, and even those results look incorrect but at least there is data.

Can this be done? What is the obvious that I am overlooking? Or am I overthinking this and it can be done simply by headcount in each hour?

**Keebellah** · 04-27-2021, 05:08 PM

Hi, welcome to the forum, and just to point out that explanation is everything, there is only one hour to the hour

many total hours are scheduled each hour

But this aside, when time crosses midnight all you have to do is add an if condition to your formula.
To check if the next hour is after midnight is count the hours until midnight and that is not 00:00:00 or 24:00:00 (because that is 0 too) but take it to 23:59:59.9 and the remaing time aftter this you just add to the previous result.
I'll download your file and take a look but this will give you an idea how to approach this.

**Keebellah** · 04-27-2021, 05:16 PM

Just took a look at your file and table, if you want a headache, you're on the right track.

You've got columns A and B which I assume are the start of probably a shift and B the end of that shift
On the first row, the header, you have the have the full day in 24 hours in one-hour columns
For a 'normal' shift starting somewhere in the day and ending before midnight, that's understood but now comes the issue
If a shot starts at 10:00 PM and ends at 07:00 AM YOU DON'T HAVVE THE COLUMNS FOR THAT.

**FlameRetired** · 04-27-2021, 05:23 PM

In the attached try this in C2 filled down and across. Yields minutes

Formula:

Please Login or Register  to view this content.

**Trippi** · 04-27-2021, 05:56 PM

Oh My Gosh! Yes this is exactly what I was trying to accomplish.

I did as you suggested in C2 and got an error (attached)

**Trippi** · 04-27-2021, 05:58 PM

I agree I have a headache. The formula did not error out, it simply returned zero.
You are correct Column A = Start Time and Column B = End Time.

**Trippi** · 04-27-2021, 06:20 PM

Originally Posted by Trippi

Oh My Gosh! Yes this is exactly what I was trying to accomplish.

I did as you suggested in C2 and got an error (attached)

Think I figured it out. SEQUENCE is not an available function for me in Office but I do have it on Office365. Going to work with this some more. MANY THANKS!

**Gregb11** · 04-27-2021, 09:32 PM

I guess I'm not understanding this correctly (which is fine), but shouldn't C2 yield 59 minutes? from 10:30pm - 7am goes across 12am so wouldn't that all be included there? Wouldn't D2:I2 also have 59 as values (etc.)?

**Keebellah** · 04-28-2021, 02:26 AM

Well, if I may intrude (again) 59 is not a full hour, I cannot alter the formula because my version does not support the sequence function but I would suggest to try the following:

Please Login or Register  to view this content.

Like I said I don't know if this will work but ...

**FlameRetired** · 04-28-2021, 03:04 AM

Thank you Keebellah. I will check it out.

**FlameRetired** · 04-28-2021, 03:51 AM

OK. This should take care of the minutes issue. I was having trouble defining the bins argument in FREQUENCY.

In the attached find a second time header to set the boundaries of the bins. In row 2 this formula to set the upper boundaries so FREQUENCY counts only the times less than or equal to the upper boundary without counting the next hour itself.

Formula:

Please Login or Register  to view this content.

Adjustments for the lower boundary (greater than) are made in the last formula. In C3 down and across to return hours.

Formula:

Please Login or Register  to view this content.

To return minutes as before

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 04-28-2021, 03:57 AM

Originally Posted by Trippi

Think I figured it out. SEQUENCE is not an available function for me in Office but I do have it on Office365. Going to work with this some more. MANY THANKS!

If you would like a non 365 version please let me know.

**Keebellah** · 04-28-2021, 04:06 AM

IMHO the table does not show the correct count when you start at 22:30 (10:30 PM) and end at 07:00 the next morning the count should be (8 * 60) + 30 but since you do not use dates you don't know this.
I would do for a Gantt-like chart that starts dynamically at midday previous day and ends at the current day midnight, that way you can visualize the actual shifts as they go
You can then even create a monthly report using a slicer
But, that will require or VBA (macros) or an extended formula with many helper columns to account for all options.

**Trippi** · 04-28-2021, 11:31 AM

I am amazed at everyone's knowledge and willingness to help. This has been a huge lifesaver and time saver. I understand the over midnight issue. I purposely left the dates off (would have been too complicated for the audience) but under normal circumstances they do exist. I would be happy to supply the data set with dates if you wanted the challenge.

**Trippi** · 04-28-2021, 11:32 AM

Originally Posted by FlameRetired

If you would like a non 365 version please let me know.

That would be much appreciated - thank you!

**Keebellah** · 04-28-2021, 11:47 AM

Me too, we learn too from other's questions and helping out, challenges are always interesting

**FlameRetired** · 04-28-2021, 07:10 PM

Here is a non 365 formula.

Formula:

Please Login or Register  to view this content.

**Keebellah** · 04-29-2021, 02:06 AM

@FlameRetired: formula doesn't work with my 2016

**FlameRetired** · 04-29-2021, 01:44 PM

You may have to array enter this one. Office 365 renders me 'blind' to that need.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

**Keebellah** · 04-29-2021, 03:42 PM

I tried that too but for some reason it still doesn't work, no issue, if the OP has the solution, I'm happy

**Trippi** · 04-29-2021, 05:20 PM

I was able to get the non 365 formula to work with a straight copy paste. First i did a copy paste to make sure I wasn't able to use SEQUENCE - got the #NAME? error. Then I took the new formula and did a copy past and the error went away and I was able to update the entire sheet.

I will say it again...blown away by the generosity and smarts! THANK YOU!

**FlameRetired** · 04-29-2021, 06:39 PM

Originally Posted by Keebellah

I tried that too but for some reason it still doesn't work, no issue, if the OP has the solution, I'm happy

Nope. Not letting me off that easily.

File attached. You want sheet count(6). I had several ideas going at once.

**Keebellah** · 04-30-2021, 02:07 AM

Nice

And I'm not letting off that easily either, since you're missing the midnight to noon of the following day

**FlameRetired** · 04-30-2021, 02:58 PM

Thank you.

The problem was in the formulas I used in the headers. The cumulative adding of times pushed those times into another (the next) day (face in palm).

Formula in D1 and across should be

Formula:

Please Login or Register  to view this content.

and in D2

Formula:

Please Login or Register  to view this content.

The 2:00 AM → 11:00 AM time slot fills in at rows 12:14 starting at column AC.

Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Re: Calculate total hours scheduled each hours of the day

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

[SOLVED] Calculate meal periods based on total scheduled hours

[SOLVED] scheduled hours total

Bookmarks

Bookmarks

Posting Permissions