Determining time off available based on earned Hours

[jomili]

Wanting an easy way to resolve the problem below:

I earn Sick days, Vacation Days, Comp Days, etc., which are given to me in terms of Hours. Right now those all add up to 1868.25 hours I can take off.
I work 8 hours per day, 5 days per week (40 hours per week), and 173.3333 hours per month (52 weeks*40 hrs per week)/12 months

My question is; if I consume all of my earned time, how many total months, weeks, days, and hours can I take off work?


Using the above numbers, using my cumbersome calculation methods, it looks to me like I can take off for 10 months, 3 weeks, 1 day, and 7 hours, but I'm not certain I've done my calculations right. I'd like to see someone else's method for calculating to see if there's an easier and/or better way to calculate. I'll post my method later, because I don't want to influence anyone who might approach this from a different angle.

Thanks in advance for any help I receive on this.

[GeoffW283]

I'm not sure about expressing your time off in terms of months as they obviously vary in size depending on exactly when you begin your time off.

So for weeks, days and hours:

- Enter the hours (1868.25) into A1

In A2 enter:

Please Login or Register  to view this content.

The result is: 46 Weeks, 3 days and 4.25 hours.

To verify this answer: 46*40 + 3*8 + 4.25 = 1868.25

[jomili]

GeoffW,
I understand your reticence on the months, but in terms of "how much time can I take off", if I tell someone 46 weeks they're going to try to translate that into months, so for my purposes I need the answer in months. Since months vary in size, I use the calculation (52 weeks*40 hrs per week)/12 months to determine average hours per month (so 173.3333...). I realize using the average makes the remaining weeks, days and hours less precise, but it does allow me to express my time in understandable terms.

My Calculations: (10 months*173.3333 hours/month)+(3 weeks* 40 hours/week)+(1 day*8 hours/day)+6.9 hours = 1868.25 hours

[GeoffW283]

OK - here is the equivalent including months per your original requirement:

Enter the hours (1868.25) into A1

In B1 enter:

Please Login or Register  to view this content.

The result in B1 (and consistent with your calculation) is: 10 months, 3 weeks, 1 days and 6.92 hours.

I can't believe your company will let you take best part of a year off!!

[jomili]

I work for the state, and they have good benefits. But they wouldn't let me take that much time off, unless I get sick or something. But you never know. Thanks for the updated formula.

Going by your initial formula I came up with this, using Named Ranges for variables:

Please Login or Register  to view this content.

it seemed to work, but the hours were off, so I was happy to see your updated version.

My initial method was a series of formulas:

Please Login or Register  to view this content.

I like your single formula better. Thanks for all your help.

[GeoffW283]

Yeah, the named ranges make the formulas a lot easier to read.
Thanks for the rep!