• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for number of scheduled FTE by hour

melvin

Member
Hello folks,
Happy Monday and hope you all are well. With the data that I have I would like assistance in a formula which shows how many FTE (Staff) are working by the hour based on the data in column G-H
G9:G14 are 6 FTE's that worked during the day for store A. I want to show that in Columns O onwards. I have above 300+ stores so cannot go doing it individually.

Appreciate your assistance

Regards,

Melv.
 

Attachments

  • FTE by hour.xls
    214.5 KB · Views: 12
As the OP suggested, the term FTE (full-time equivalent) implies no more than 'staff member' here since a part-timer is either fully present or absent at any given hour.

I am slightly concerned that the example worksheet uses an obsolete format; does that imply a need to cater for a legacy version of Excel?
It is also unclear what should be shown when the staffing level changes on a half hour; 0.5 would be possible but would not discriminate between the first and second part of the hour.

73539
 
Hi Peter,
When the staffing level changes on 0.5, it is assumed that it reflects in the first half hour.
As an example the first B starts at 6.5, I would drop the 0.5 from the 6-7am block, but capture only 0.5, rather than the full 1 FTE.

Appreciate your assistance with this.

Melv.
 
No reason why it shouldn't work provided you keep the formulas and data consistent. Either the time in hours or Excel time values are possible but they must be consistent between the in/out clock times and the defined time slots.
 
I tried to sue 0.5, but I am sure getting something wrong. As I added the "Time" vs the data set, I can see I am doubling up, as am not been able to rbeak the 0.5

Melv.
 

Attachments

  • FTE by hour v1.xls
    997 KB · Views: 3
As the OP suggested, the term FTE (full-time equivalent) implies no more than 'staff member' here since a part-timer is either fully present or absent at any given hour.
I'm quite aware of that. My point is that OP's should avoid using professional jargon if they want everybody to understand them. ( and to get help easier )
A little respect for those helping for free on their spare time is not too much to ask is it ? :rolleyes:
 
@pecoflyer I wasn't trying to be patronising, I just took your statement at face value. Sorry, for missing the point.

I also feel I am not the best person to deal with this question. The fact that the workbook is an .xls gives me problems and my normal 365 strategies of FILTER and dynamic array formulas are not applicable.

@melvin What version of Excel are you using? For example, I would normally suggest converting your data to an Excel Table but not if your version of Excel predates Office 2007. I would also observe that it is not necessary to convert the serial times to hours to make the formulas work.
The manpower levels I calculated apply at a specific instant in time; they are not intended to provide a calculation of the hours worked since there is no assurance that a staff member that was on-shift at the start of the hour actually worked the whole hour. In the case of your latest workbook, summing rows now gives an estimate of the number of 'half-hours' worked, so that figure will be double the number of manhours.
 

Attachments

  • FTE by hour v1.xls
    770.5 KB · Views: 7
Thanks guys. Much appreciated. appreciate you trying .
Unfortunately the outcome is not what I am looking for. Hopefully I get some bite's on the question posted. Have a good day
cheers
 
No problem, but I suspect the next action is yours. You wish to calculate
"How many FTE (Staff) are working by the hour"
but that number could change every 15 minutes. If the actual case were
13:00-13:15 1
13:15-13:30 1
13:00-13:45 0
13:45-14:00 2
what would you like to see for the hour? Total manhours = Average = 1, the minimum manning level = 0, a series 1-1-0-2, a max/min range 0-2?
 
Hey Pete,
For the objective I am trying to achieve what I would like to achieve is in that 1 hour, at any given point in time we had 1 staff available.
i
13:00-13:30 1 >>>>>>>>>Start with 1 staff first half hour
13:30-14:00 +1 ........................Another joined. By now we have 2 staff
14:00-14:30 -1 .............................One staff left for the day, bringing it back to 1 staff, however as soon this clocks off the next one joins in.
14:30-15:00 +1 ...........................Another came in


So split in half hour we had
1 in first half hour
2 in the next half hour
3 half hour one left and one joined. so left with 2 in that half hour.

I dont have the smart to fomulate this. Unfortunately the data I am getting from our labor system should have done this for me, but it comes with absolutely messed up dataset
 
This may work, I do not know what version of Excel you are using.
I have tried to provide alternative reporting for man-hours deployed and the minimum manpower for each hour. Below the table there is a breakdown by the quarter hour for a single store. There is a lot of calculation to the workbook is already starting to slow.
7367473675
 

Attachments

  • FTE by hour v1 (1).xlsx
    192.4 KB · Views: 0
melvin
If You can ... wait
... then You could use something like this
by pressing [ Do It ]-button
 

Attachments

  • FTE by hour v1.xlsm
    106.8 KB · Views: 2
This is a beauty. Thanks Vletm. The only point I did not understand is at 15o'clock there should be 2 staff members.
As the 1st staff logs off at 15:30 and line 12 there should be someone starting at 14o'clock. SO technically there should be 2 staff members at that time.
73685
 
melvin
You are looking for FTE by hour ...
Yes, at 15o'clock, there is 2 staff members ... but the first left 30 minutes later
... is that staff as FTE` ... Full Time ... between 15-16?
I tried to show case below ....
Screenshot 2021-03-10 at 09.24.02.png
It's possible get something like above with 15/30/60/120-minutes-scale... below [ Do It ]-button.
Screenshot 2021-03-10 at 11.04.32.png
... as well as Employees data by select as above.
Same way could show employees data with 'HCC-result-table'...

There are none challenges to get needed output ... after You could give exact needs and rules.
 

Attachments

  • FTE by hour v1.xlsm
    118.4 KB · Views: 0
Last edited:
Obviously, I encountered the same problem in reporting.
73687
If one focusses upon Store 2212 one sees it is reported as closed from 07:00 to 08:00 despite there being 1 man-hour deployed over the hour (see post #14). The explanation becomes obvious when the quarter hourly report is viewed. No staff were on the premises from 07:00-07:30 so one has to assume the store was closed.

There would be less ambiguity were the reporting period 15min but the table layout becomes unwieldly for multiple stores -- a matter of swings and roundabouts.
 
melvin
I tested something sample while waiting ... updated file 12-Mar

If You select any of those employee data - You could see employee data as below
Screenshot 2021-03-11 at 14.21.05.png

If You select any 'Home Cost Center'-value - You could see its data as below
... now, You could see how many employees are as You want.
Screenshot 2021-03-11 at 14.21.27.png

... testing continue ...
The chart can get from 'rawdata' too - by selecting cell.
... this is possible without 'waiting' after [ Do It ].
... and if 'HCH' selection - then could see number of employees per 15minutes with blue line.
Screenshot 2021-03-11 at 20.53.15.png

If You click that chart - it will hide.

As well as - those sorting options will work with employee data by select header.
... I tested only some normal selection as this is a sample!
 

Attachments

  • FTE by hour v1.xlsm
    149.4 KB · Views: 5
Last edited:
Hey mate, i was trying to study the file so that I can scale up with my further analysis myself. Could not understand how these columns get updated when I click a store 2216.

Appreciate if you could share the trick.
73821
 
melvin
Scale
Your sample gives an image that You're using one day data.
There are fixed scale for one day as well as automatic for number of employees.
Those columns are for charts, which You found in #19 reply.

How...
There ia a part of code for those, which will be run after select certain cells without any tricks.
 
Back
Top