How to generate data

[AthmanArumugam]

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.

 

[Pratik Parmar]

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

 

[AthmanArumugam]

Hi Prathik - Thanks very much for the above solution. Helped me big time.