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]

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]

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


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

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]

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]

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]

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

View attachment 64353

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]

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]

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]

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.xlsx
	G	H	I	J	K	L
9			COMPUTED AGE
10	TODAY'S DATE		0	0	0
11	BIRTHDAY		0	0	0
12	SUM TOTAL		25	4	11
13	PLUS 1 FOR END DATE		↓	↓	12
14	SUM TOTAL		0	0	0
15	MINUS YEARS EMPLOYED		0	0	0
16			18	10	6
Quality Assurance Check She (2)

Cell Formulas
Range	Formula
I12	I12	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")
J12	J12	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)
K12	K12	=DATEDIF(DATE($C$3+$O12,$D$3+$P12,$E$3),DATE($C$2,$D$2,$E$2),"d")
K13	K13	=K12+1
I16	I16	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")
J16	J16	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)
K16	K16	=DATEDIF(DATE($C$3+$O16,$D$3+$P16,$E$3),DATE($C$4,$D$4,$E$4),"d")

 

[Anthony47]

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]

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 AGE
TODAY'S DATE		0	0	0
BIRTHDAY		0	0	0
SUM TOTAL		25	4	9264
PLUS 1 FOR END DATE		↓	↓	9265
SUM TOTAL		0	0	0
MINUS YEARS EMPLOYED		0	0	0
		18	10	6884

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

2022	5	12
1996	12	30
2015	11	5
2022	2	4
6	3	0

YEARS EMPLOYED
2021	14	5
2015	11	5
6	3	0