HH:MM Time format calculations.. What Gives? 1

[kjv1611]

I'm embarrassed to ask. Skip, if you read this, please cover your eyes.

This is related to my question, thread68-1772892

Here's some sample data as to what I have to work with:

[pre]
[A] [C] [D] [E] [F] [G] [H]
MonthName XtraTime OnCheck TimeOff Balance Earned FinalTime FirstDayOfMonth
January [formula] manual 1/1/2017
February [formula] =F2+B2 2/1/2017
March [formula] =F3+B3 3/1/2017
April [formula] =F4+B4 4/1/2017
May [formula] =F5+B5 5/1/2017
June [formula] =F6+B6 6/1/2017
July [formula] =F7+B7 7/1/2017
August [formula] =F8+B8 8/1/2017
September [formula] =F9+B9 9/1/2017
October [formula] =F10+B10 10/1/2017
November [formula] =F11+B11 11/1/2017
December [formula] =F12+B12 12/1/2017
[/pre]
Sorry I didn't bother filling in the other times, but it's basically all times listed in HH:MM - so examples can be in any field: 100 hours and 30 minutes shown as 100:30.

To the right of the above region, I have a new region I added to help make things easier to understand what I'm doing. That section looks like:

[pre] [J] [K] [L]
Worksheet F.LastName Extra Hours
Anniversary 6/2/2003
6 Years 6/2/2009 8:00
15 Years 6/2/2018 80:00[/pre]

The concept shown here is basically after 6 years, an employee gets an extra 8 hours time off, and then each year thereafter, that bumps up 8 ours per year until maxing out at 15 years service, where thereafter, it's an extra 80 hours each year.

In Column B is the problematic formula. I have times listed all over the sheet in HH:MM but in the formulas I have in column B, in one of the conditions, the Excel calculations do not like my attempt at math. I am sure that I just need to use some additional functions to tell it that I want to multiply the hours and minutes by an integer to come up with hours and minutes. I imagine I need to convert the hours and minutes to just minutes or seconds, do the calculation, and then convert back to hours and minutes.

The formula looks like this:

=IF(MONTH(H3)=MONTH($K$3),IF(YEAR(H3)<YEAR($K$4),"",IF(YEAR(H3)<YEAR($K$5),[highlight #FCE94F](DATEDIF($K$4,H3,"Y")+1)[b]*8[/b],$L$5))[/highlight],"")

I am certain the issue is in the [highlight #FCE94F]*8[/highlight] piece. I built this formula for a personal workbook I already put together for myself, and I'm trying to convert it to work for everyone else. Of course, I've got to go back and change mine sometime now to keep tit for tat with what the company uses.

I'll dig back into this first thing tomorrow morning.

The general concept is:
Take the number of difference between an employee's anniversary date and the date of the given month, and if that's between 6 and 15, multiply 8 times that number. 8 is the number of hours added for each additional year of service between 6 and 15.

I think the fix will be to convert the HH:MM time to seconds, run the multiplication, and then convert back to HH:MM.

If any of you have a better mousetrap in mind or other suggestions, I'm all ears. Thanks in advance for any thoughts.

Yes, I realize I'll probably be embarrassed if/when one of you points out something simple I'm missing.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hi,

Your HH:MM formatted values intended to be duration, like 100:30 is Date/Time. The underlying value for 100:30 is 4.1875 DAYS.

Do you really want to work with days for this vacation benefit? Might be a lot easier to simply work with HOURS.

As for your formula, what is the logic for this? I'd guess that a lookup table might be more appropriate than a honkin' long formula. Use INDEX/MATCH with a Greater Than lookup Match Type.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

As far as hours vs HH:MM, that came as a request because a new tool was introduced for tracking time. Previously, they recorded time in decimal values, but since the time punch software gives its results in HH:MM format, they wanted to change the vacation tracking to the same format. It was working 100% while using HOURS in decimal format.

The formula logic works until it multiplies by 8. It's multiplying the HH:MM value by 8, but I think it needs to multiply by a decimal.
The general concept is:
[ol 1][li]Take the number of difference between an employee's anniversary date and the date of the given month,[/li]
[li]If that's between 6 and 15, multiply 8 times the number of years different[/li]
[li]8 is the number of hours added for each additional year of service between 6 and 15[/li]
[li]If the person has worked 15+ years, then automatically add 80 hours each anniversary[/li][/ol]

=IF(MONTH(H3)=MONTH($K$3),IF(YEAR(H3)<YEAR($K$4),"",IF(YEAR(H3)<YEAR($K$5),(DATEDIF($K$4,H3,"Y")+1)*8,$L$5)),"")

In my formula:
DATEDIF($K$4,H3,"Y")+1)*8 -> This is the part that breaks. It worked 100% correctly in decimal format.

DATEDIF($K$4,H3,"Y") -> Gives the # of years between the anniversary date (first month of work / or first month tracked for vacation purposes)

+1 -> Adds one because when I do the calculation on the actual same month, the calculation drops a year. If I went to the following month, it'd be correct. Adding one fixes it easily.

*8 -> This takes the # of hours added for each year over 6 years. So if the employee has been here 8 years, that'd be 3 years of extra time (6, 7, 8) times 8 hours for each, for a total of 24 addtl hrs.

Anyway, yeah, I did think about setting up a full table, since it's only a handful of years: 6 through 15. I may end up doing that to keep it simpler.

Thanks for that suggestion. I'll keep a copy of the formula to the side, and see how using the table with INDEX(MATCH()) works out. I think it will indeed be far simpler.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

So far, my INDEX(MATCH()) setup isn't working correctly. Either I'm thinking about something wrong, or perhaps I need a different widget.

Here's one example formula:
=IF(MONTH(H8)=MONTH($K$4),INDEX(K4:M14,MATCH(YEAR(H8),L4:L14,0),2),"")

The values in the new table are:

[PRE] [J] [K] [L] [M]
YOS Date Year Extra Hours
Anniversary 6/2/2003 2003 0:00
6 Years 6/2/2009 2009 8:00
7 Years 6/2/2010 2010 16:00
8 Years 6/2/2011 2011 24:00
9 Years 6/2/2012 2012 32:00
10 Years 6/2/2013 2013 40:00
11 Years 6/2/2014 2014 48:00
12 Years 6/2/2015 2015 56:00
13 Years 6/2/2016 2016 64:00
14 Years 6/2/2017 2017 72:00
15 Years 6/2/2018 2018 80:00[/PRE]

In this example, I'd expect that # to return 72:00 or 72 hours from the 14 years box.

I don't know. Now this seems convoluted, or else my brain is in a fog.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I now have a working solution. It may not be the most elegant, but it works.

I went back to my original setup and formula, but only with slight modifications.

So the reference table for extra hours now looks like this:

[pre] [J] [K] [L]
[3] Worksheet F.LastName Extra Hours
[3] Anniversary 2/24/1986
[4] 6 Years 2/24/1992 8
[5] 15 Years 2/24/2001 80[/pre]

The new formula looks like (in values in Row 3):

=IF(MONTH(H3)=MONTH($K$3),IF(YEAR(H3)<YEAR($K$4),"",IF(YEAR(H3)<YEAR($K$5),((DATEDIF($K$4,H3,"Y")[highlight #FCE94F]+2)/24)*8[/highlight],$L$5/24)),"")

I highlighted the changes.
[ul][li]add 2 to the calculated number of years instead of original +1 for it to work for whatever reason[/li]
[li]divide by 24 while just using a # in the Excel reference cell at L4 since Excel sees a random whole number more as days than hours[/li][/ul]

But now it seems to work.

I may eventually revisit it if we think it doesn't work well enough. And I wouldn't mind revisiting anyway to make the thing easier to read and understand what's going on.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zelgar]

Why don't you use the YEARFRAC function:

=IF(MONTH(H3)=MONTH($K$3),IF(YEAR(H3)-6>=YEAR($K$4),(INT(YEARFRAC($K$4,H3))-5)*8,0),0)/24

If the number format is in DATE format, you need the /24 at the end to get the result in HH:MM format. If you want to see the result in the general or number format get rid of the /24 at the end of the formula.

 

[SkipVought]

First, whatever you decide regarding HH:MM, just keep in mind the UNITS of the underlying value. In this case you'll have DAYS, Convert days to hours or hours to days and calculate accordingly.

Regarding the lookup, I made your table a Structured Table named tYOS
[tt]
=INDEX(tYOS[Extra Hours],MATCH(H8,tYOS[Date],1),1)
[/tt]

Whatever date you have in H8, it will return the Extra Hours.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Thanks. You make it look too easy.

I'm sure that works, I'm not even going to test. I may eventually revisit and try it whether we set up a full table and use that or not.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57