# Date Calculation Errors (YY|MM|DD)



## Newbienew (May 9, 2022)

Good Day all, 

           Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work.  The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1. 

            My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated. 

QA Workbook (click here)


----------



## Anthony47 (May 9, 2022)

You can probably take advantage of the function DATEDIFF, that returns the difference between two dates
See DATEDIF function
Its syntax: *DATEDIF*(*start_date*,*end_date*,*unit*)
Beware that the "unit" has to be entered in English even the name of the function is translated in your language


----------



## Newbienew (May 9, 2022)

@Anthony47  No doubt that might work out but I do not have that in my version of excel


----------



## Anthony47 (May 10, 2022)

It's there, probably it's not adverticed
Which version do you have? In which language?

Probably a google search would return the name in your languade

Beware that I mistyped the name of the function (DATEDIFF), but the remaining message clearly say "DATEDIF"


----------



## Anthony47 (May 10, 2022)

We are on quite different time-zones, this discussion could last for one week at this pace...
Try this:
Insert in a new workbook the following macro:

```
Sub DDTest()
'
Range("A1").Formula = "=DATEDIF(DATEVALUE(""23/01/2021""),DATEVALUE(""23/05/2022""),""M"")"
End Sub
```
Then run the macro DDTest: it will write in A1 of the active sheet a DATADIF formula in your language, so you can discover the name used by your Excel version


----------



## Newbienew (May 10, 2022)

In my version, I only have Date and Datevalue. I ran the code and it gave me #Value!


----------



## Rick Rothstein (May 10, 2022)

You definitely have the DATEDIF function... the problem is it is an undocumented function. I am sure Anthony's code gave you an error because the order of his dates (d/m/y) is not the same as yours (which is probably m/d/y). Try his code this way and it should work for you...

```
Sub DDTest()
    Range("A1").Formula = "=DATEDIF(DATEVALUE(""01/23/2021""),DATEVALUE(""05/23/2022""),""M"")"
End Sub
```


----------



## bebo021999 (May 10, 2022)

Try with VBA:

```
Sub test()
Range("C8").Formula = "=DATEDIF(DATE(C4,D4,E4),DATE(C5,D5,E5+1),""y"")"
Range("D8").Formula = "=DATEDIF(DATE(C4,D4,E4),DATE(C5,D5,E5+1),""ym"")"
Range("E8").Formula = "=DATEDIF(DATE(C4,D4,E4),DATE(C5,D5,E5+1),""md"") "
Range("C26").Formula = "=DATEDIF(DATE(C4,D4,E4),today(),""y"")"
Range("D26").Formula = "=DATEDIF(DATE(C4,D4,E4),today(),""ym"")"
Range("E26").Formula = "=DATEDIF(DATE(C4,D4,E4),today(),""md"")"
Range("I17").Formula = "=DATEDIF(DATE(C3,D3,E3),today()-DATE(C5,D5,E5+1)+DATE(C4,D4,E4),""y"")"
Range("J17").Formula = "=DATEDIF(DATE(C3,D3,E3),today()-DATE(C5,D5,E5+1)+DATE(C4,D4,E4),""ym"")"
Range("K17").Formula = "=DATEDIF(DATE(C3,D3,E3),today()-DATE(C5,D5,E5+1)+DATE(C4,D4,E4),""md"")"
End Sub
```
With cells formula added via VBA is highlighted as red


----------



## Anthony47 (May 11, 2022)

I asked which Office  version you use, no response.
I asked which language you use, no response.



> In my version, I only have Date and Datevalue. I ran the code and it gave me #Value!


You have to read which formula has been set into the cell, not the value returned
At that point you finally know haw DATEDIF is translated in your language

Bye


----------



## Newbienew (May 11, 2022)

Rick Rothstein said:


> You definitely have the DATEDIF function... the problem is it is an undocumented function. I am sure Anthony's code gave you an error because the order of his dates (d/m/y) is not the same as yours (which is probably m/d/y). Try his code this way and it should work for you...
> 
> ```
> Sub DDTest()
> ...


This version of this did work out and my result was a 16. However, I might be lost in translation of the function of the code. I think I understood it as it would show me the equivalent formula to  DATEIF on my version. From there would be the incorporation of it. I would like to learn more about how to use this in my project here.


----------



## Newbienew (May 9, 2022)

Good Day all, 

           Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work.  The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1. 

            My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated. 

QA Workbook (click here)


----------



## Anthony47 (May 11, 2022)

The code was only to let you translate DATEDIF to your unknown language. See the microsoft link for the description of the function
In in your project there are dates, than the function will let you calculate the difference in  Y, M and D


----------



## Newbienew (May 11, 2022)

Anthony47 said:


> I asked which Office  version you use, no response.
> I asked which language you use, no response.
> 
> 
> ...


My sincerest apologies. I thought my version was displayed under my name. 2016 is my windows version, not sure what you mean by language. I hope I am not being silly by saying english.


----------



## Anthony47 (May 11, 2022)

I confirm we see 2016 as your Office version. If the language is English then the function is named DATEDIF.
Unfortunately there are many languages in the world, and Microsoft translate the name of the Functions in the used language.
Having you repeated that Datedif was not available for you I guessed you was happily working with a Samoan version

If you have problem in coding DATEDIF in your project then make an example of what you would like to calculate and for sure someone will help


----------



## Rick Rothstein (May 11, 2022)

Newbienew said:


> My sincerest apologies. I thought my version was displayed under my name. 2016 is my windows version, not sure what you mean by language. I hope I am not being silly by saying english.


Did you see my post in Message #7 yet?


----------



## Newbienew (May 11, 2022)

bebo021999 said:


> Try with VBA:
> 
> ```
> Sub test()
> ...


I see the results of this VBA which worked out the same as yours. My issue the J12 formula is supposed to be 4 but it remains 0. Not sure how I fudge it up. And K16 is 9 but should be a 12. I would ultimately like to show the steps to get to the answers to double-checking purposes. Thank you very much for your time on this project I have.


----------



## Newbienew (May 11, 2022)

Rick Rothstein said:


> Did you see my post in Message #7 yet?


Yes, I did see your posting. I thought I replied to your message. I do apologize if it did go thru.


----------



## Newbienew (May 11, 2022)

Anthony47 said:


> I confirm we see 2016 as your Office version. If the language is English then the function is named DATEDIF.
> Unfortunately there are many languages in the world, and Microsoft translate the name of the Functions in the used language.
> Having you repeated that Datedif was not available for you I guessed you was happily working with a Samoan version
> 
> If you have problem in coding DATEDIF in your project then make an example of what you would like to calculate and for sure someone will help


Thank You so much as I learned something new today about the language. I believe this is also what Rick Rothstein was eluding to in a way. The layout of my project is the actual format. My formulas in each cell works and doesn't work depending on the numbers in the calculation. As you see the computed age has a 4 that should have dropped down to where the zero. Instead, it's a 0 there and the 3 is now subtracted from the 0 making it -3 instead of a 1. Where this is all simple subtraction math my formulas have issues interacting with one another and giving the correct results every time. Perfecting this is a challenge for me as I have been working on this for several months and trying not to as for help after the first time but I can't figure out why it's off or not working to get me such a simple answer. 
Thank you very much for your time and assistance with my project. I normally never work with VBA as I suck at it.


----------



## Alex Blakenburg (May 11, 2022)

If this doesn't help ignore it but I thought I would give it a try:
You only seem to need the current age and the age at hire date, so I have not filled in all the other calcs.

On a COPY of you work book paste this into G9.
(I didn't mean to leave the formula at K13, not sure what that is meant to do)

QACHECK v02.xlsxGHIJKL9COMPUTED AGE10TODAY'S DATE00011BIRTHDAY00012SUM TOTAL2541113PLUS 1 FOR END DATE↓↓1214SUM TOTAL00015MINUS YEARS EMPLOYED0001618106Quality Assurance Check She (2)Cell FormulasRangeFormulaI12I12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")J12J12=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)K12K12=DATEDIF(DATE($C$3+$O12,$D$3+$P12,$E$3),DATE($C$2,$D$2,$E$2),"d")K13K13=K12+1I16I16=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")J16J16=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)K16K16=DATEDIF(DATE($C$3+$O16,$D$3+$P16,$E$3),DATE($C$4,$D$4,$E$4),"d")


----------



## Anthony47 (May 11, 2022)

Don't know whether you solved using Alex's formulas (see above), but let me anyway make an example to calculate for "Years Employed", cells C6:E6

I'll consider that you need to include in the count both the first and the last day of your dates.
For the years, in C6 `=DATEDIF(DATE(C4,D4,E4),DATE(C5,D5,E5+1),"Y")`
For the months, in D6 `'=DATEDIF(DATE(C4,D4,E4),DATE(C5,D5,E5+1),"YM")`
For the days, in E6 `=DATE(C5,D5,E5+1)-EDATE(DATE(C4,D4,E4),C6*12+D6)`
You'll see that I didn't use, for the Days, Datedif; this is because Microsoft reports that using "MD" may result in "inaccurate result"

Hope this helps


----------



## Newbienew (May 13, 2022)

I am soo sorry for the delay in reply. I have been extremely overtask at work and a couple of members of my team are pretty ill. 
@Alex Blakenburg- I attempted to use the formula but it did not work out for me. The days calculated out to be 6884. I am not sure if this is due to the use of the date hired verse todays date.
COMPUTED AGETODAY'S DATE000BIRTHDAY000SUM TOTAL2549264PLUS 1 FOR END DATE↓↓9265SUM TOTAL000MINUS YEARS EMPLOYED00018106884


@Anthony47 - This section is all hand jammed in, except the today's date. All of this info feeds the other four sections. (Computed age, Time apart from work, years employed). However, I did place your formula in row 16 of C,D, and E and it works perfectly there. 
2022512199612302015115202224630



YEARS EMPLOYED20211452015115630


----------



## Newbienew (May 9, 2022)

Good Day all, 

           Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work.  The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1. 

            My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated. 

QA Workbook (click here)


----------



## Newbienew (May 13, 2022)

I am soo sorry for the delay in reply. I have been extremely overtask at work and a couple of members of my team are pretty ill.
@Alex Blakenburg- I attempted to use the formula but it did not work out for me. The days calculated out to be 6884. I am not sure if this is due to the use of the date hired verse todays date. I want to make sure that each block of the formulas I have works the way they are supposed to get the outcome you have provided.
COMPUTED AGETODAY'S DATE000BIRTHDAY000SUM TOTAL2549264PLUS 1 FOR END DATE↓↓9265SUM TOTAL000MINUS YEARS EMPLOYED00018106884

I adjusted J14 formula to display the number 4 as it didnt before.

QACHECK.xlsxGHIJKL8-1 Year+12 months+30 days9COMPUTED AGE10TODAY'S DATE2021164211BIRTHDAY1996123012SUM TOTAL2541213PLUS 1 FOR END DATE↓↓1314SUM TOTAL2441315MINUS YEARS EMPLOYED6301618113Quality Assurance Check SheetCell FormulasRangeFormulaI10I10=IF(D3<=D2,C2,C2-1)J10J10=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12,K12I12=I10-C3J12J12=+J10-D3K10K10=IF(E2<E3,E2+30,E2)K13K13=K12+1I14I14=IF(J14<J15,I12,I12-1)J14J14=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=(I14-C6)+IF(J16>30,1,)J16J16=+IF(K16>30,J14-J15+1,J14-J15)K16K16=IF(K14-K15>30,"0",K14-K15)




@Anthony47 - This section is all hand jammed in, except the today's date. All of this info feeds the other four sections. (Computed age, Time apart from work, years employed). However, I did place your formula in row 16 of C,D, and E and it works perfectly there.
2022512199612302015115202224630



Sorry for the double posting.


----------



## Alex Blakenburg (May 13, 2022)

Newbienew said:


> I attempted to use the formula but it did not work out for me. The days calculated out to be 6884. I am not sure if this is due to the use of the date hired verse todays date.


That would happen if you didn't increase the start date values in the formula like I had, to allow for what was already accounted for in the Year and Month columns.

In K12 I had:

```
=DATEDIF(DATE($C$3*+$O12*,$D$3*+$P12*,$E$3),DATE($C$2,$D$2,$E$2),"d")
```

And in K16 I had

```
=DATEDIF(DATE($C$3*+$O16*,$D$3*+$P16*,$E$3),DATE($C$4,$D$4,$E$4),"d")
```


----------



## Newbienew (May 15, 2022)

I copied each one in to the each cell. 
QACHECK.xlsxGHIJKL8-1 Year+12 months+30 days9COMPUTED AGE10TODAY'S DATE2021164411BIRTHDAY1996123012SUM TOTAL254926613PLUS 1 FOR END DATE↓↓926714SUM TOTAL244926715MINUS YEARS EMPLOYED6301618106884Quality Assurance Check SheetCell FormulasRangeFormulaI10I10=IF(D3<=D2,C2,C2-1)J10J10=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12I12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")J12J12=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)K10K10=IF(E2<E3,E2+30,E2)K12K12=DATEDIF(DATE($C$3+$O12,$D$3+$P12,$E$3),DATE($C$2,$D$2,$E$2),"d")K13K13=K12+1I14I14=IF(J14<J15,I12,I12-1)J14J14=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")J16J16=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)K16K16=DATEDIF(DATE($C$3+$O16,$D$3+$P16,$E$3),DATE($C$4,$D$4,$E$4),"d")


----------



## Alex Blakenburg (May 15, 2022)

I had my table in a different position to what was in your spreadsheet.
Try this:

20220511 DateDif etc QACHECK v02 Newbienew.xlsxGHIJK8-1 Year+12 months+30 days9COMPUTED AGE10TODAY'S DATE2021164511BIRTHDAY1996123012SUM TOTAL2541513PLUS 1 FOR END DATE↓↓1614SUM TOTAL2441615MINUS YEARS EMPLOYED6301618106Sheet1Cell FormulasRangeFormulaI10I10=IF(D3<=D2,C2,C2-1)J10J10=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12I12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")J12J12=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)K10K10=IF(E2<E3,E2+30,E2)K12K12=DATEDIF(DATE($C$3+$I12,$D$3+$J12,$E$3),DATE($C$2,$D$2,$E$2),"d")K13K13=K12+1I14I14=IF(J14<J15,I12,I12-1)J14J14=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")J16J16=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)K16K16=DATEDIF(DATE($C$3+$I16,$D$3+$J16,$E$3),DATE($C$4,$D$4,$E$4),"d")


----------



## Newbienew (May 19, 2022)

@Alex Blakenburg I attempted to dabble with your formula this week but I still can't figure out how to make it work with the days. I really wanted to message back with I got it but umm yea.


----------



## Alex Blakenburg (May 19, 2022)

Did you copy the Xl2BB in my post and paste it to G8 in your spreadsheet ?


----------



## Newbienew (May 22, 2022)

Alex Blakenburg said:


> Did you copy the Xl2BB in my post and paste it to G8 in your spreadsheet ?


Thant I did good Sir


----------



## Alex Blakenburg (May 22, 2022)

You did and it still didn't work or you just did and it worked ?


----------



## Newbienew (Dec 1, 2022)

Alex Blakenburg said:


> You did and it still didn't work or you just did and it worked ?


So I updated the link and added comments that I hope to explain better. In the posting above with your formula when you do it by hand the end result should be 19 1 16
My issue is each cell needs to feed the next cells based on the old stack math method.  

Each step needs to be shown as I have people above my level that will still do it by hand or occasionally I will do it by hand as I did the one above.

Updated Sheet


----------



## Alex Blakenburg (Dec 1, 2022)

It might be because it was so long ago but I am no really understanding what the we are trying to fix. Are we talking about this section below ?
What is not working and what should it show ?
What are you trying to calculate, it seems to be trying to calculate the age at the point of employment using the manually entered employment period on row 6 (which does not seem to be calculated using the hire date) ?

QACHECK-1.xlsxGHIJK9COMPUTED AGE10TODAY'S DATE2022113211BIRTHDAY198991812SUM TOTAL3321413↓↓1514SUM TOTAL3321515MINUS YEARS EMPLOYED1101316COMPUTED AGE2222Quality Assurance Check SheetCell FormulasRangeFormulaI10I10=IF(D3<=D2,C2,C2-1)J10J10=IF(D3>=D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12,K12I12=I10-C3J12J12=+J10-D3K10K10=IF(E2<E3,E2+30,E2)K13K13=K12+1I14I14=IF(J12<J15,I12-1,IF(J14>=J15,I12,""))J14J14=IF(K13<K15,J12-1,IF(J12<J15,J12+12,J12))K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=(I14-C6)+IF(J16>30,1,)J16J16=IF(K16>30,J14-J15+1,ABS(J14-J15))K16K16=IF(K14-K15>30,"0",K14-K15)


----------



## Newbienew (May 9, 2022)

Good Day all, 

           Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work.  The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1. 

            My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated. 

QA Workbook (click here)


----------



## Newbienew (Dec 2, 2022)

Yes we are trying to fix the issue above (below)
The way it looks above is exactly how it should function. However, depending on the date, it will not function properly. The main issues are rows 10 and 14
Such as entering the birthday 1989  12 1, on today's day you will see the month will become 24 when it should stay 12, making the sum 0. That will drop down to the following cell j14 and remain the same unless k15 is more than k14 making it 12 borrowing 12 months (1 year) from I14. I14 seems to remain the same when it should be 1 less. 

The years worked are subtracted from row 14, providing the computed age answer. 

My formulas in rows 10 and 14 are not able to determine when to add numbers when they are supposed to and minus numbers from the next cell over like it suppose to. 

Try plugging in your dates and how many years you have been with the company. It should spit out how old you were when you started. 

It's more of how old you would be minus the years you worked. So if you worked for 10 years and started at the age of 20 but are now, you're 30, this would show the calculation for that. To put it simply. I just get it down to the day.


----------



## Alex Blakenburg (Dec 2, 2022)

I can probably figure out how to make your method work but it is a rather unusual approach.

Is there any reason you can't use the below ? 
Copy it into G9 on a copy of your workbook (or a copy of the sheet will work)
I only needs rows 12 & 16 since it gets everything else from the input cells in the top left.
Note: It is not using the Years employed line but is calculating it from the Hire Date.
I have also not done the +1  we can add that if you think you need to.

20221202 Dates QACHECK-1 Newbienew.xlsxGHIJKLMNO9COMPUTED AGE10TODAY'S DATE202212211BIRTHDAY198912112AGE330113↓↓2<PLUS 1 FOR END DATE14SUM TOTAL -13215MINUS YEARS EMPLOYED1101316COMPUTED AGE @ EMP DATE2150Quality Assurance Check Date DiCell FormulasRangeFormulaI10I10=IF(OR(D3<D2,AND(D3=D2,E3<E2)),C2,C2-1)J10J10=IF(OR(D3>D2,AND(D3=D2,E3>E2)),D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12I12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")J12J12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m")-I12*12K10K10=IF(E2<E3,E2+DAY(EOMONTH(DATE(I10,J10,1),0)),E2)K12K12=DATE($C$2,$D$2,$E$2)-EDATE(DATE($C$3,$D$3,$E$3),I12*12+J12)K13K13=K12+1I14I14=IF(J12<J15,I12-1,IF(J14>=J15,I12,""))J14J14=IF(K13<K15,J12-1,IF(J12<J15,J12+12,J12))K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")J16J16=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m")-I16*12K16K16=DATE($C$4,$D$4,$E$4)-EDATE(DATE($C$3,$D$3,$E$3),I16*12+J16)


----------



## Newbienew (Dec 5, 2022)

I see what you're getting at, but that puts the member at the age of 17, and it leaves the other cells blank or add up incorrectly. The date hired is only used for the number of years employed. Which all in all is not needed for this sheet as those numbers are on the document they turn in. 

i have attached the link with your formulas attached. Hopefully I didn't place it wrong


----------



## Alex Blakenburg (Dec 5, 2022)

Newbienew said:


> I see what you're getting at, but that puts the member at the age of 17, and it leaves the other cells blank or add up incorrectly. The date hired is only used for the number of years employed. Which all in all is not needed for this sheet as those numbers are on the document they turn in.
> 
> i have attached the link with your formulas attached. Hopefully I didn't place it wrong


I can see no link. To send a sample spreadsheet you need to put it on a shared server such as Google drive, drop box, one drive etc make it available to anyone with the link and post the link here using the chain link button.

As it stands I have no visibility of when you are getting 17 and what you think it should be.

You need the hire date to calculate the years employed and it is more likely to be entered correctly than a user calculated years employed. Most people want to and it would be generally accepted as preferable to automate as much of the process as possible minimising the possibility of user error.


----------



## Newbienew (Dec 7, 2022)

Sorry, thought I added it

QA Check

The years employed is not a calculation as that is on the documentation given to us. Everything in the left hand corner is provided.


----------



## Alex Blakenburg (Dec 7, 2022)

Give this a try (copy into G9)

20221202 Dates QACHECK-2 Newbienew 20221208.xlsxGHIJK9COMPUTED AGE10TODAY'S DATE2021233211BIRTHDAY198912412SUM TOTAL32112813↓↓2914SUM TOTAL32112915MINUS YEARS EMPLOYED1101316COMPUTED AGE211115Quality Assurance Check SheetCell FormulasRangeFormulaI10I10=IF(OR(D3<D2,AND(D3=D2,E3<E2)),C2,C2-1)J10J10=IF(OR(D3>D2,AND(D3=D2,E3>E2)),D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)I11:K11I11=C3I12I12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")J12J12=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m")-I12*12K10K10=IF(E2<E3,E2+DAY(EOMONTH(DATE(I10,J10,1),0)),E2)K12K12=DATE($C$2,$D$2,$E$2)-EDATE(DATE($C$3,$D$3,$E$3),I12*12+J12)K13K13=K12+1I14I14=IF(J12<J15,I12-1,IF(J14>=J15,I12,""))J14J14=IF(K13<K15,J12-1,IF(J12<J15,J12+12,J12))K14K14=IF(K15>K13,K13+30,K13)I15:K15I15=C6I16I16=DATEDIF(
                   DATE( $C$3, $D$3, $E$3 ),
                   DATE( $C$2 - $I$15, $D$2 - $J$15, $E$2 - $K$15 ),
                   "y")J16J16=DATEDIF(
                   DATE( $C$3, $D$3, $E$3 ),
                   DATE( $C$2 - $I$15, $D$2 - $J$15, $E$2 - $K$15 ),
                   "m")
   - (I16*12)K16K16=DATE( $C$2-I15, $D$2-J15, $E$2-K15 ) -
  EDATE( DATE( $C$3, $D$3, $E$3 ), I16 * 12 + J16 )


----------



## Newbienew (Dec 8, 2022)

I tested it out and typed in all the numbers for any issues. Any days below for the birthday three leave I14 and J14 blank. If the day is 2 Cell I10 goes down 1 year, I14 remains blank, and j14 becomes -1. 

k15 and k16 do not add up to k14 but equal k12.


----------



## Alex Blakenburg (Dec 8, 2022)

I am afraid that unless you can show me when Rows 12 and 16 are returning incorrect results I am going to have to leave it there.

Your "helper" row 14 which is what you are currently referring to is not actually very useful. It seems to be trying to get the accuracy down to 1 day and by relying on +30 that is only going to happen in 5 months of the year. To get the formula on row 14 right you would need to replicate the formulas I have in row 16 and if that is the case you may as well just use Rows 16 & 12 to calculate it.


----------



## Newbienew (Dec 17, 2022)

Alex Blakenburg said:


> I am afraid that unless you can show me when Rows 12 and 16 are returning incorrect results I am going to have to leave it there.
> 
> Your "helper" row 14 which is what you are currently referring to is not actually very useful. It seems to be trying to get the accuracy down to 1 day and by relying on +30 that is only going to happen in 5 months of the year. To get the formula on row 14 right you would need to replicate the formulas I have in row 16 and if that is the case you may as well just use Rows 16 & 12 to calculate it.


Sorry for the late reply. I had a personal situation that needed my full attention. I do see your point, but this is more of a training/verification tool. So showing all the steps of how it works is needed. This is why the setup is the way it is. We have before used how long the member was with the organization minus their current age. This works for a few of our processing applicants but not all. We would be off by one year or two, and the paperwork would be kicked back for reprocessing, to which the handwritten method of what you see would be used.


----------



## Alex Blakenburg (Dec 19, 2022)

I'm afraid I can help you fix rows 12 & 16 if you believe they are producing incorrect results but there is no way of getting the row 14 correct without it replicating most of the calculation from Row 16 or simply doing the maths from using rows 12,15,16.  I don't see how that is going to be helpful from a training prespective.


----------



## Newbienew (May 9, 2022)

Good Day all, 

           Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work.  The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1. 

            My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated. 

QA Workbook (click here)


----------



## Newbienew (Dec 21, 2022)

Alex Blakenburg said:


> I'm afraid I can help you fix rows 12 & 16 if you believe they are producing incorrect results but there is no way of getting the row 14 correct without it replicating most of the calculation from Row 16 or simply doing the maths from using rows 12,15,16.  I don't see how that is going to be helpful from a training prespective.


Got it. Thank you very much for all you have done. I am very grateful.
I look forward to your expertise in the future


----------



## Alex Blakenburg (Dec 21, 2022)

Appreciate you having followed up on this. Sorry it wasn't exactly what you were hoping for.


----------



## Newbienew (Dec 27, 2022)

You good Sir have done a lot. I have no complaints.


----------

