# Figuring vacation hours - My brain is fried

#### Special-K

##### Board Regular
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
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
I shall give it a try as soon as my head stops spinning.
Thank you for your generous help!

Kevin.

#### Special-K

##### Board Regular
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
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
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

Replies
3
Views
579
Replies
1
Views
275
Replies
3
Views
284
Replies
1
Views
483
Replies
6
Views
99

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?

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