# Excel 2010: link two cells with formula to count days and hours

402 Views 25 Replies 3 Participants Last post by  :z:
I'm updating a spreadsheet that currently manually logs employee vacation time. It's formatted in two cells like this:

20D | 0.00H
(meaning 20 days, 0 hours; the "D" and "H" are added automatically in the formatting)

As vacation days are reduced, let's say by 4.75 hours one week, then I manually remove a day and add the balance of 3.25 hours like this:

19D | 3.25H

My first thought was to put them in a single cell tabulated on just the hours, but I couldn't find a way to format it to show it as the "20D 0.00H" format.

So I'm thinking I need a linked formula that rolls over to the days when the hours cell reaches 8.00, but I don't even know where to begin to do that or what to search for. I have very limited knowledge of Excel formulas, but it seems like this would be something I could do with Excel.

Any help would be appreciated. Thanks in advance!
1 - 20 of 26 Posts
would you be able to post an example
I have a sample that uses Days/Hours/Mins

But I think you are using fractions of a day - anyway

=FLOOR(A2*3,1)&" Days and "&TEXT(SUM(A2,-TIME(8,0,0)*FLOOR(A2*3,1))," h") & " Hours " & MINUTE(A2) & " Minutes"

Then i'm using 160 hours in a cell formatted [H]:MM
which
=FLOOR(A2*3,1)
will change to 20 days
so you can add or subtract based again on hours and mins
As you need to change the base date to get negative time, i have changed a formula to use 2 cells , rather than make that change

BUT - This maybe nothing like you want , and uses more cells then needed

#### Attachments

• 9.7 KB Views: 19
See less See more
@etaf

Thank you very much for your reply. I think that's going to be more complex than what I ultimately need, but I don't have my head wrapped around the syntax yet and maybe all of this really is needed.

I should have shared some additional information in my first post which would have added more clarity:
● We bill in quarter-hour increments only (so hours will always be reported as #.##H – decimal format)
● We have a very flexible vacation policy and employees who work more than 40 hours can either take overtime pay or comp their overage for more vacation time. Additionally, vacation hours roll over from the prior year, so the 20-day starting point is just a sample for reference. Everyone will have different "base" starting points from year to year. And because of the comping, vacation hours can be both subtracted and added.

I hope this doesn't overcomplicate things. I don't need to link any of the other cells, just the vacation ones. The whole point of this exercise for me is to avoid me making an adding or subtraction error by doing the adjustments manually each week. So I just need the syntax to do the addition and subtraction for vacation days and hours. I hope that makes sense, but perhaps I'm being too naive on how it will need to work.

I have attached a stripped-down spreadsheet with just the basics. Thanks again for your help.

#### Attachments

• 13.9 KB Views: 19
See less See more
you may need VBA to update a cell and change based on an entry - not an area i cover here
as it appears you may be changing every week, not sure of the process you use

where will you put the addition/subtraction times ......
and they will need to stay for each week

otherwise it may require VBA to update the actual cell
@etaf

I apologize because I don't think I've been clear enough in my explanations (or I'm just not smart enough to grasp yours).

Ideally, I would just add the hours in the hours cell (manually, I suppose), and then it would automatically roll over to the days column if under 0.00 or greater than or equal to 8.00.

E.g.: if there's a row that reports 19D in the hours cell and 6.50H in the hours cell and someone used 7.75 hours, I could then enter -1.25 into the hours cell. I realize now this is contrary to my original message, because I'm still doing a bit of manual math, but it'd still be better than what I have now if the days cell then took that information and said "you can't have less than zero in the hours cell; subtract a day from the days cell, add 8 hours to the hours cell, then subtract the 1.25 hours" to get 18D in the days cell and 6.75H in the hours cell.

Does that make the syntax simpler? Thanks again for your continued help.
This is normally all done as hours
May I ask how many hours of annual leave do they accumulate for a standard 40 hour week
in addition to PeterOz request

PeterOz may have a better solution, so its worth also answering PeterOz and seeing what answer you get
I maybe overcomplicating this or not fully understanding and PeterOz may know the solution

anyway
As its a decimal and not hours and mins , then it will be just numbers and NOT Hrs Mins as i first thought
so negative should be fine.

i'm assuming you will manually enter the starting Base at the beginning of the year for each person , as they maybe all different
say 20D 0H

if there's a row that reports 19D in the hours cell and 6.50H in the hours cell and someone used 7.75 hours, I could then enter -1.25 into the hours cell.
How does someone show they have used 7.75 hours in the spreadsheet

perhaps some manually entered examples would help here with a note explaining how arrived

We have a very flexible vacation policy and employees who work more than 40 hours can either take overtime pay or comp their overage for more vacation time. Additionally, vacation hours roll over from the prior year, so the 20-day starting point is just a sample for reference. Everyone will have different "base" starting points from year to year. And because of the comping, vacation hours can be both subtracted and added.
These are just theoretical questions , and in the real spreadsheet may not happen - but just to get an understanding of what you require

1) Just work a Normal 40H week all week - what happens
if someone just works 40H a week all year - then I assume its just the base time - or as PeterOz mentiones do they earn holiday , as when someone first starts a job, some companies do not give the full 20D or prorater it depending on start date - BUT give so many days/hours worked each month

2) working more than 40H
If someone works an extra 5 hours every week, then they will get an additional 5 hours to their holiday
Each week it May/would go up by 5 assuming no vacation taken and 45 hours worked every week.
by the end of the year (in theory, i know would not happen ) , assuming every week was 45 hours , they would have worked 52 * and extra 5 = 260H - a DAY is classed as 8 hours
260 Hours = 260/8 32.5 (the .5 is half a day - so 4 hours ) 32D and 4H

how do you enter into the spreadsheet when they have worked the 45 hours and that they want some or all of that comped - added to the vacation or maybe a proportion of it - say they worked 45H , and want 2H holiday and 3H paid - how is that shown

3) working less than 40H
Now every week they only work 35 hours ....... what happens ????? and again how shown that they want it comped and not paid

4) Planned holiday
they take a holiday of 2 weeks - how is the 10 days entered into the spreadsheet to take the 10days - off the vacation time.
Similar if they take half a day 4H - how is that entered

As i say , i maybe overcomplicating and miss-understanding - so sorry about that
See less See more
I've added a few sample lines as requested – see attachment. I think that may address your questions as well @etaf.

When I took over this role and put all this data in a spreadsheet (used to be done by hand on photocopied paper), I naturally set it up to all be in hours, but the employees revolted against that because they were used to knowing how many full days they had at a glance. If it came down to it, I'd even be fine with an hours-only column and then a column next to it that reports the same figure in days and hours.

Thanks, guys, for sticking with me here. I'm sorry I haven't been clear enough.

#### Attachments

• 14 KB Views: 11
Here's the best work-around I've found, which allows me to update the hours in one cell and report the days and hours in the adjacent cell:

=QUOTIENT(E3,8)&"D "&((E3/8)-QUOTIENT(E3,8))*8&"H"

The only problem with this method is it doesn't seem to have an option to format the hours with two decimal places when it's a half-hour increment (i.e. 0.50). I have the formatting set to two decimal places, but the formula ignores that, so it reports 19D 3.5H instead of 19D 3.50H to keep the text aligned with other cells that have a quarter-hour increment.
not checked the formula at all
=QUOTIENT(E3,8)&"D "&TEXT(((E3/8)-QUOTIENT(E3,8))*8,"0.00")&"H"
this should give 2 decimal places for hours
1
That did the trick – thanks!

So, final thought: I have two cells still, but is there any way to apply the formatting of the days and hours cell to the adjacent hours cell?

Example: cell E3 has just a number in it (e.g. 160), which is the total hours, and E4 reads E3 and converts it to 20D 0.00H. Can the cell with the formula be hidden elsewhere and apply its formatting to E3?
This is my take - Basically the same - Sheet 1
Is this linked to a timesheet - overtime should come from a time sheet
not a manual add or do you not use an excel time sheet

#### Attachments

• 21.3 KB Views: 22
Question
As per your spreadsheet do you only have 2 employees?
If yes,the carryover is wrong
@PeterOz

Yes, there are only two salaried employees at this time. I get what you're saying regarding linking it to the weekly timesheets, and I see what you're doing with the sample you shared, but I'm not sure I'm ready for that level of complexity yet. I do plan to link the existing cells together once I get my subject question answered, though.

I have a work-around solution from @etaf now and an ideal solution for me at this point would be to figure out how to apply the desired formating (##D #.##H formatting) to the Vacation cell without having two cells. If it can't be done, I'll run with the two-cell solution.

Thanks!
Column i is one cell
I know you have an answer you are happy with
If you decide to do roll over something like this should work

#### Attachments

• 21.9 KB Views: 11
1
@PeterOz
I can see the formulas in column I on the new attachment now – thanks for that. However, it's still relying on the other cells I don't currently have to work.

I don't know if the final step I want is possible. It is essentially a formatting question, but I'm hoping I can apply the formatting from a hidden cell out of site and just update the hours. If it's not possible, I think I've reached the end of what I can do, short of enhancing it per your adjustments.

Example: cell E3 has just a number in it (e.g. 160), which is the total hours, and E4 reads E3 and converts it to 20D 0.00H. Can the cell with the formula be hidden elsewhere and apply its formatting result to E3?
can you attach what you have
@PeterOz

It's not much yet, but here you go. I'm going to finish it once I know which direction I'm going on the vacation hours presentation.

#### Attachments

• 13.9 KB Views: 12
If you have too many columns, you can hide e3
Or is it you do not want other people seeing the formula
Can the cell with the formula be hidden elsewhere
Yes
and apply its formatting result to E3?
No you would have a circular reference
1
1 - 20 of 26 Posts