Formula to Calculate Items by Associates and Time to Account for Hours - End of Day

[JacobGFMR]

Hello,

I have a data set that looks like the following: 

Count	Associates
400	20

There is a rate per the count, and he overall goal is to determine how long it will take the 20 associates to complete the 400 items at a specific rate of 6, or so, items per hour and then determine an estimated time of day to be complete. 

The work to determine the overall output, I think, would like this:

Manager	Count	Associates	Rate	Amount of Time (Minutes)	Estimated Time of Day Completed
Smith	400	20	7	142.8	142.8 minutes added to current time
John	300	10	6	180	180 minutes added to current time
Roger	200	15	9	120	120 minutes added to current time

The final output is needing to look something like this:

Manager	Estimate Time of Day Completed
Smith	3:00
John	3:40
Roger	2:40

Thanks. 

[mceleavey]

Hi @JacobGFMR ,

Are you saying the rate is the number of items that can be completed per Associate per hour? 

M.

[Luke_C]

Hi @JacobGFMR 

Here's an example of a way to do this:

First we do the calculation to find out how many minutes it will take (Count/Associates * Rate)

Then, we use the datetimeadd function to add those minutes to the current time. I then used datetime format to put the time into a more readable format. 

more datetime function info

[JacobGFMR]

The rate is the rate the associates should be completing the items by. 

6 items per hour = rate of 10 minutes per item. 

[Luke_C]

@JacobGFMR 

Updated the formula based on that new info

[JacobGFMR]

This is great @Luke_C.

One last question, though.

How can I format the time to show Eastern time and not my local (Central) time? 

[Luke_C · Accepted solution]

Hi @JacobGFMR 

You could update the second formula to the below. I'm not aware of any functions that update for time zones. 

datetimeformat(datetimeadd(datetimenow(),[Time (Mins)]+60,'minutes'),'%I:%M:%S %p')

[JacobGFMR]

Great! 

Thank you very much for the help with this. 

This is exactly what I needed.