Marklarbear
Board Regular
- Joined
- Nov 6, 2003
- Messages
- 119
- Office Version
- 365
- Platform
- Windows
H All,
I’ve broken my brain on excel and need assistance…..
I’ve built a doc to help me calculate the number of staff working by 15 minute interval per day of the week. I have around 50 staff but they all work different days & hours in the day. I seem to be having issues at certain times of the day in the calculations in my spreadsheet.
I do a data dump (from an outside source) into excel into a range of cells… to simplify it a bit, assume I dump the following (1 staff member as an example):
Tab 1
Start times into column A - ie cell A1 will have value 19/08/2019 9:00:00 AM
Stop times into column B - ie cell B1 will have value 19/08/2019 6:00:00 PM
(cells are formatted as category custom, type d/mm/yyyy h:mm)
I have the following formulas cells A5 & B5 (to remove the dates from the start and stop times):
Cell A5: =A1-INT(A1)
Cell B5: =B1-INT(B1)
The data in cell A5 shows as 9:00
The data in cell B5 shows as 18:00
(cells are formatted as category Time, type 13:30)
Tab 2
In Tab 2, I have a range of cells that counts the number of staff working by 15 minute intervals based on the data in Tab 1:
Starting at cell C3 and going across to cell H3, I list the shift number:
C3 = 1
D3 = 2
E3 = 3
F3 = 4
And so on
Starting at cell C4 and going across to cell H4, I reference the start times from Tab 1:
C4 =’tab 1’!A5
And so on
Starting at cell C5 and going across to cell H5, I reference the stop times from Tab 1:
C5 =’tab 1’!B5
And so on
Cells C3, C4, C5 return the following values:
1
8:00
17:00
(rows 4 & 5 are formatted as category custom, type h:mm)
In cell C6 I have the following formula:
=if(‘Tab 1’!A5>0,1,””)
This formula returns the value of 1 if there is a start time in Tab 1 cell A5. If there’s no value in Tab 1 cell A5 it returns nothing.
From here is where the magic starts…..
Still in Tab 2…
From cell B8 to cell B103 I have every 15 minute interval of the day listed ie
B8 = 12:00:00 AM
B9 = 12:15:00 AM
B10 = 12:30:00 AM
And so on, to cell B103 = 11:45:00 PM
(all rows are formatted as category custom, type h:mm)
In cell C8 I have the following formula:
=IF(AND($B8>=C$4,$B8<C$5),C$6,"")
This returns a value of 1 if the start time is equal to or greater than midnight AND the stop time is less than midnight.
(all rows are formatted as category general)
The formula in cell C8 is repeated all the way down to cell C103. The only change is to the reference to cells in column B.
Therefore the formula in cell C44 should show as:
=IF(AND($B44>=C$4,$B44<C$5),C$6,"")
Which will return the value of 1 (as the start time for shift 1 is 9:00am).
Cell C45 will return a value of 1 and so on until cell C76. Cell C76 will return no value as the shift finish’s at 5pm (which is correct for what I want it to do).
This is repeated for shift 2 in column D, shift 3 in column E and so on…..
The issue I have:
For whatever reason the following times return no value (they should return a 1): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM. Also…
For whatever reason the following times return value of 1 (they should return no value): 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.
So if I have a shift that starts at 8:00 AM, the first return of the value 1 is in the 8:15 AM interval – the first return of the value 1 should be in the 8:00 AM interval.
Same thing at the other end of the shift…. If I have a shift that finish’s at 8:00 PM, the last return of the value 1 should be in the preceding interval at 7:45 PM.
All other shift start times / stop times return the correct values… it’s just ): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM, 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.
(it seems coincidental that they are all 3 hours apart??)
If I manually type in the start / stop times into the data dump section in Tab 1 everything works fine….. but that defeats the purpose of having this “automated”…. And also why would it be that the data dump works fine for a shift that starts at 7:59 AM and at 8:01 AM but not for a shift that start at 8:00 AM ?
Any assistance would be greatly appreciated.
I’ve broken my brain on excel and need assistance…..
I’ve built a doc to help me calculate the number of staff working by 15 minute interval per day of the week. I have around 50 staff but they all work different days & hours in the day. I seem to be having issues at certain times of the day in the calculations in my spreadsheet.
I do a data dump (from an outside source) into excel into a range of cells… to simplify it a bit, assume I dump the following (1 staff member as an example):
Tab 1
Start times into column A - ie cell A1 will have value 19/08/2019 9:00:00 AM
Stop times into column B - ie cell B1 will have value 19/08/2019 6:00:00 PM
(cells are formatted as category custom, type d/mm/yyyy h:mm)
I have the following formulas cells A5 & B5 (to remove the dates from the start and stop times):
Cell A5: =A1-INT(A1)
Cell B5: =B1-INT(B1)
The data in cell A5 shows as 9:00
The data in cell B5 shows as 18:00
(cells are formatted as category Time, type 13:30)
Tab 2
In Tab 2, I have a range of cells that counts the number of staff working by 15 minute intervals based on the data in Tab 1:
Starting at cell C3 and going across to cell H3, I list the shift number:
C3 = 1
D3 = 2
E3 = 3
F3 = 4
And so on
Starting at cell C4 and going across to cell H4, I reference the start times from Tab 1:
C4 =’tab 1’!A5
And so on
Starting at cell C5 and going across to cell H5, I reference the stop times from Tab 1:
C5 =’tab 1’!B5
And so on
Cells C3, C4, C5 return the following values:
1
8:00
17:00
(rows 4 & 5 are formatted as category custom, type h:mm)
In cell C6 I have the following formula:
=if(‘Tab 1’!A5>0,1,””)
This formula returns the value of 1 if there is a start time in Tab 1 cell A5. If there’s no value in Tab 1 cell A5 it returns nothing.
From here is where the magic starts…..
Still in Tab 2…
From cell B8 to cell B103 I have every 15 minute interval of the day listed ie
B8 = 12:00:00 AM
B9 = 12:15:00 AM
B10 = 12:30:00 AM
And so on, to cell B103 = 11:45:00 PM
(all rows are formatted as category custom, type h:mm)
In cell C8 I have the following formula:
=IF(AND($B8>=C$4,$B8<C$5),C$6,"")
This returns a value of 1 if the start time is equal to or greater than midnight AND the stop time is less than midnight.
(all rows are formatted as category general)
The formula in cell C8 is repeated all the way down to cell C103. The only change is to the reference to cells in column B.
Therefore the formula in cell C44 should show as:
=IF(AND($B44>=C$4,$B44<C$5),C$6,"")
Which will return the value of 1 (as the start time for shift 1 is 9:00am).
Cell C45 will return a value of 1 and so on until cell C76. Cell C76 will return no value as the shift finish’s at 5pm (which is correct for what I want it to do).
This is repeated for shift 2 in column D, shift 3 in column E and so on…..
The issue I have:
For whatever reason the following times return no value (they should return a 1): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM. Also…
For whatever reason the following times return value of 1 (they should return no value): 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.
So if I have a shift that starts at 8:00 AM, the first return of the value 1 is in the 8:15 AM interval – the first return of the value 1 should be in the 8:00 AM interval.
Same thing at the other end of the shift…. If I have a shift that finish’s at 8:00 PM, the last return of the value 1 should be in the preceding interval at 7:45 PM.
All other shift start times / stop times return the correct values… it’s just ): 2:00 AM, 5:00 AM, 8:00 AM, 11:00 AM, 2:00 PM, 5:00 PM, 8:00 PM, 11:00 PM.
(it seems coincidental that they are all 3 hours apart??)
If I manually type in the start / stop times into the data dump section in Tab 1 everything works fine….. but that defeats the purpose of having this “automated”…. And also why would it be that the data dump works fine for a shift that starts at 7:59 AM and at 8:01 AM but not for a shift that start at 8:00 AM ?
Any assistance would be greatly appreciated.
