Figuring vacation hours - My brain is fried

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

cmhoz

Active Member
Joined
Aug 20, 2006
Messages
268
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

Board Regular
Joined
Apr 18, 2006
Messages
63
I shall give it a try as soon as my head stops spinning.
Thank you for your generous help!

Kevin.
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
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

Active Member
Joined
Aug 20, 2006
Messages
268
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

Board Regular
Joined
Apr 18, 2006
Messages
63
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 :rolleyes:

Thank you for all the help you're giving me :)
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top