• 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.

How to generate data

hi,

I have the total number of hours worked by each employee in a month.
I need to split this hours randomly across four weeks. I have around 65 employees for whom I need to do this activity. Is there any simple way or function to achieve this.

Find attached the excel file.
 

Attachments

Pratik Parmar

New Member
Hello Kindly See Below.

I assume you know the F4 shortcut key to use Absolute/Relative Referencing method. I have used for making calculations

Logic:
1) Divide Decimal Hours by 24 in next column (Work Hours will be converted to days)
2) Divide Decimal Hours by Total no. of Weeks below the Weeks columns
In your case is 5
3) Ensure to Set "Cell Format" to display as Time format by pressing Ctrl+1 & Select "Custom" & [h]:mm:ss
e.g. 180 --> 180:00:00 ( It converts

4) Make a Tabular data table for 30 Days to display weekly your data 31 Days Calendar

5) Now to populate with time data, Divide Week Hours by No. of days of that week.
e.g.

Week 1 = 4 Days
Week 2 = 5 Days
Week 3 = 5 Days
Week 4 = 5 Days
Week 5 = 3 Days

6) Then again Set "Cell Format" to display as Time format by pressing Ctrl+1 & Select "Custom" see below line
[h]:mm:ss

Results


I've attached the file for your perusal.
Make sure you always learn the Logic in Excel.

Regards
Pratik Parmar

Note: Give a reply to ensure your problem has been solved
 

Attachments

Last edited:
Top