Date Calculation Errors (YY|MM|DD)

[Newbienew]

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]

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]

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

 

[Anthony47]

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]

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]

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

 

[Rick Rothstein]

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]

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]

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]

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

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.