Figuring vacation hours - My brain is fried

[Special-K]

Drats.

I've been going over this time and again but am just not good enough to figure this out. Please help.

I've got a vacation sheet. Sometimes people take whole days ... sometimes half days ... and sometimes hourly vacation.

Some people are union and work a different number of hours than others.

So, CELL G111 has a drop-down list to choose how many hours this person works in a shift.

I've got it totaled where:
A person who works 7.5 hours in a day
Takes TWO WHOLE DAYS = 15 Hours
Takes 1/2 DAY ................. = 3.75
Takes 1 Hour .................... = 0.1333333333

I TOTAL these up to equal = 18.883

Then, I divide that sum by the variable number of hours (in cell G111) this person works per shift. In this case (7.5 hours) ... which gives me a total of = 2.51778

Now ... notice my total (2.51778) represents (2 and 1/2 days .... )

How can I get this number to cleanly report (2.5 days 1 Hour)

There is more to this which is why I'm going this long route ... but can some Excel genius help me?

Thank you.

 

[cmhoz]

Okay, this might also be the long way around, but it was the first thing I thought of!

Assume:

A1 = hours in a day (7.5)
A3 = total (18.883)

I have figured out days and hours seperately here...

B5 = days
B6 = hours

1. Days =ROUND(A3/A1,1)
2. Hours =(A3-(B5*A1))/0.133

then... concatonate them to make it pretty:

=B5 & " Days and " B6 " Hours"

This should give you 2.5 Days and 1 Hour

Cheers

 

[Special-K]

I shall give it a try as soon as my head stops spinning.
Thank you for your generous help!

Kevin.

 

[Special-K]

Out of luck so far ...

Well, I tried the formulas you suggested ... and perhaps I am doing something wrong (very possible) ... but I'm not getting the desired results.

Here's why ...

Let's say a person took the following vacations:
6 HOURS ......
2 DAYS ......
1/2 DAY .......
1 HOUR ......

This should total 2.5 DAYS and 7 HOURS

In the DAYS cell I'm getting a total of "2.6" ... and in the HOURS cell I'm getting a total of 1.375 hours.

Am I using your ideas improperly?

 

[cmhoz]

Ah... yes, I see the problem.

Change the formula in the days cell to say:

=ROUNDDOWN((A3/A1)/0.5, 0)*0.5

Cheers

 

[Special-K]

I think the problem is bigger than it appears

I made the adjustment ... and it fixed a part of the problem.

But here's where it falters:

Say the person adds on another HOUR of vacation ... (Keep in mind that the variable in cell G111 says this person works 7.5 hours a day)

So ... before, the person had taken (2.5 DAYS and 7 HOURS)
But with the added hour, the total should climb to to (roughly) 3 DAYs and X hours.

But the way these formulas work, I've now got:
2.5 DAYS in one cell ... and 8 HOURS in the other.

This is why I originally was going to go with the grand total in numeric form (19.81666666667) divide that by the variable in Cell G111 (7.5 Hours) and somehow break that to read X Days and X Hours)

This is well over my head

Thank you for all the help you're giving me