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

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

@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.

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.

Click to expand...

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.

Click to expand...

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