Excel Timesheet Question

Hello. I am creating a timesheet which keeps track of employee's vacation and sick time.

The problem I have is converting hours to days:hours:minutes given that there are 7.5 hours in a work day.

I understand that Excel keeps track of time in 24ths of a day, so for my situation, 7.5/24 = .3125.

The problem I'm facing is that Excel still assumes that I want to show hours and minutes worked as if we were working 24 hours a day when we are only working 7.5 hours a day.

For example, let's say an employee currently has 80.25 hours of vacation time accrued. What formula would I need to write to calculate this time into a dd:hh:mm format?

Let's assume cell A1 shows 80.25. How do I get cell A2 to represent this time in days, hours and minutes format, keeping into consideration that every 7 hours and 30 minutes remaining accounts for an additional workday?

Currently, my spreadsheet reads as follows:

A1 Value=80.25

A2=(A1/24)/0.3125

A2 Format=dd:hh:mm

A2 Value: 10:16:48

As you can see, 16:48 actually would add 2 more work days (15 hours) to the day value, so instead of this reading 10:16:48, it should read 12:1:48.

This is somewhat difficult to write into words, so I hope someone can understand my dilemma.

Any help anyone could provide will be much appreciated.

Thanks

Originally Posted by FSchleyhahn

Hello. I am creating a timesheet which keeps track of employee's vacation and sick time.

The problem I have is converting hours to days:hours:minutes given that there are 7.5 hours in a work day.

I understand that Excel keeps track of time in 24ths of a day, so for my situation, 7.5/24 = .3125.

The problem I'm facing is that Excel still assumes that I want to show hours and minutes worked as if we were working 24 hours a day when we are only working 7.5 hours a day.

For example, let's say an employee currently has 80.25 hours of vacation time accrued. What formula would I need to write to calculate this time into a dd:hh:mm format?

Let's assume cell A1 shows 80.25. How do I get cell A2 to represent this time in days, hours and minutes format, keeping into consideration that every 7 hours and 30 minutes remaining accounts for an additional workday?

Currently, my spreadsheet reads as follows:

A1 Value=80.25

A2=(A1/24)/0.3125

A2 Format=dd:hh:mm

A2 Value: 10:16:48

As you can see, 16:48 actually would add 2 more work days (15 hours) to the day value, so instead of this reading 10:16:48, it should read 12:1:48.

This is somewhat difficult to write into words, so I hope someone can understand my dilemma.

Any help anyone could provide will be much appreciated.

Thanks

According to my calcs based on a 7.5 hour day, 80.25 hours translates to 10 days, 5 hours 15 minutes. In which case you could use the following User Defined function in the VBE, and then enter into a cell

=Holidays(80.25)

The result is then : 10 : 5 : 15

Please Login or Register  to view this content.

HTH

Wow! That looks pretty complicated. Nevertheless, I am going to pursue this approach, but I'm not for sure how to use the VBE. I opened my worksheet and am in the VBE but I haven't the slightest idea on where to put the code you wrote for me--would you mind replying with that information?

Thanks again for your help!