how much time do I have?

[jomili]

I have 1747.25 hours of various leave accumulated.

I work 8 hours per day, 5 days per week, 12 months per year.

Is there a single formula I could use to figure out the max time I could take off in month, weeks, days and hours, for instance "10 months, one day and 5.9 hours"?

I have a methodology, but it involves lots of formulas, and I think there must be a simpler way.

My current way:
Hours per month = (40*52)/12 =173.333333

Total Months = RoundDown(Total hours/Hours per Month,0) =10 months
Total Weeks = Rounddown((Total Hours-(Total Months*hours per month))/40 hours per week) = 0 weeks
Total Days = Rounddown((Total Hours-(Total Months*hours per month)-Total week*Hours per week))/8 hours per day = 1 day
Total Hours = RoundDown(Total Hours-(Total Months*hours per month+Total weeks*hours per week+Total Days*8 hours per day,1) = 5.9 hours

[Olly]

One approach:

Please Login or Register  to view this content.

[jomili]

Olly,

My TotalHours are in cell M22, I did your formula like this:

Please Login or Register  to view this content.

Result was:

Please Login or Register  to view this content.

Not sure how the "Today" function figures in. Also, we have a difference between the days (I have 1) the hours (I have 5.9). Also no "Weeks" variable.

[shg]

Not sure how the "Today" function figures in.

Today() is relevant because the lengths of months vary.

Wouldn't it be easier to just calculate the day you would need to return if you took off tomorrow, using a WorkDay() function? That way you could account for holidays.

[jomili]

Shg,
Not really, for this one. I'll be retiring in about 4 years. My saved up leave balances (sick, comp, vacation, etc.) count toward my retirement. So, I'd like to know how much time is being applied to my years, which is the way my retirement revenues are calculated.

[shg]

I'm still not getting it. This wouldn't tell you what you want to know?

	A	B	C
1	Accumulated Leave, hours	1747.25	B1: Input
2	Eligible Retirement Date	31 Jul 2021	B2: Input
3	Date I Can Split	30 Sep 2020	B3: =WORKDAY(B2, -INT(B1/8))

[jomili]

Nope. I won't be retiring on my eligible retirement date, but on a date still to be determined (approximately 4 years, but could be 6 or 7)

These leave balances add to my years of service when I retire. The years of service determines my annual payout after retirement. So, I want to know how many years, months, days etc. I'm adding in, so I can check that against my current years of service to determine the payout level I'll get so I can figure out the "right" time to retire. Conversely, at any day I could be hit by a bus, get cancer, etc. It's nice to know how much time I can take off just by taking my leave, all of which is reported to me in terms of hours.

[shg]

OK. But again, months vary in length, so the exact answer depends on what date you start counting from.

[Olly]

As shg says, months are not constant length, which means you need some fixed date to work from, to calculate months accurately.

Do you really want months and weeks? I've rarely seen them combined... but here's a modification to add a week count as well:

Please Login or Register  to view this content.

[jomili]

Olly, Even though months aren't constant, I define them as a constant in my first post:

Please Login or Register  to view this content.

I think the formula isn't giving the right results. For instance, with total hours = 1747.25, the first and second formulas you posted give:

Please Login or Register  to view this content.

My original formulas agree on months and weeks, but I have 1 day, and 5.9 hours.

If I add 24 to my total hours, that should be 3 days added. So with total hours of 1771.25, your first and second formulas give this:

Please Login or Register  to view this content.

whereas my formulas show 4 days.

[Olly]

As I said, my formulae work with a real calendar, and a real start date, rather than notional constant 'months'. So when you add 24 (working) hours, it adds 3 working days - which from today, brings us into a Saturday morning, a non-working day, so your Saturday and Sunday (non-working days) are included in your total time remaining.

Let's work through a simple example - and for simplicity, let's use the latter formula I posted

If TotalHours is 8, that's 1 day - today. Dead simple.
0 Years, 0 Months, 0 Weeks, 1 Days, 0 Hours, 0 Minutes

If TotalHours is 24, that means I am taking 3 working days including today. Today is a Tuesday, so those three days off are Tue, Wed, and Thu:
0 Years, 0 Months, 0 Weeks, 3 Days, 0 Hours, 0 Minutes

Now if TotalHours is 32, that means I am taking 4 working days including today. Today is a Tuesday, so those four days off are Wed, Thu and Fri and Monday. Sat and Sun are already non-working days. So the result is 6 days off:
0 Years, 0 Months, 0 Weeks, 6 Days, 0 Hours, 0 Minutes

Hopefully that explains the difference between adding 3 days and adding 5 days, when you add 24 working hours. It's including a weekend.


As for the constant months - well, that doesn't make any sense. A total of weeks would make sense, as they ARE constant. But we'll never reach the same value for months, as no real month matches your defined 'average' month duration.

[jomili]

I understand where you're coming from, and I think what's throwing us off is TODAY(). I'm not interested in any particular date, today, tomorrow, whatever. I have a sum of hours. What I want to know is, for that sum of hours, how Months (each month containing 173.3333 hours), weeks (each week containing 40 hours), days (each day containing 8 hours), and hours (leftover) does that evaluate to?

[Olly]

And the point is that the number of months that relates to will vary, depending on the start (or end) date, as months don't have a fixed number of working hours in them (unlike weeks).

[jomili]

As I've said, there is no definite start or end date. I'm using the results for planning. I can just use my original formulas, just thought there might be a better way to do it.