Years, Months, Weeks, Days

[Robert Wyatt]

trying to write a formula to give years, months, weeks, and days, but having a little trouble could someone tell me how to do this including a sample of what I'm working on, I have formulas in the Years, Months, Days box's , but weeks is where I'm having my trouble can someone help?

Book1
	F
6	10
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(B6="","",(DATEDIF(B6,TODAY(),"MD")))

 

[awoohaw]

Sort of unsure what you're asking for. Your mini sheet really doesn't give any insight to your question. Can you show the starting values and expected results?

But, here are two ways that may help....

Or are you asking for week number of the year?

Also, what constitutes a week in your scenario? Any day in a new week? What day does your week counting start with?

Book1
	A	B	C	D	E	F	G
1
2				Start Date	End Date	# Mondays	Integer of Div 7
3				Tue 2023-05-16	Wed 2023-05-17	0	0
4				Tue 2023-05-16	Mon 2023-05-22	1	0
5				Tue 2023-05-16	Mon 2023-05-29	2	1
6				Tue 2023-05-16	Thu 2023-06-15	4	4
7				Tue 2023-05-16	Mon 2023-07-31	11	10
8				Tue 2023-05-16	Mon 2024-05-13	52	51
9				Tue 2023-05-16	Thu 2024-05-16	52	52
Sheet1

Cell Formulas
Range	Formula
F3:F9	F3	=NETWORKDAYS.INTL(D3,E3,"0111111")
G3:G9	G3	=INT((E3-D3)/7)

 

[johnny51981]

Excel, currently, does not offer Weeks as a unit for the DATEDIF formula. So, you will have to do the Days version and divide it by 7.

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com

 

[C Moore]

Better Excel formula converts days into Years, Months, Days

Here’s a better Excel formula for converting a number of days into text for year, months and days. This formula leaves out zero values and proper singular/p

office-watch.com

 

[Robert Wyatt]

Sorry hopefully this will clear things up

Book1
	B	C	D	E	F
7	Hire In Date	Years	Months	Weeks	Days
8	2/4/2008	15	6		10
Sheet1

Cell Formulas
Range	Formula
C8	C8	=IF(B8="","",(DATEDIF(B8,TODAY(),"Y")))
D8	D8	=IF(B8="","",(DATEDIF(B8,TODAY(),"YM")))
F8	F8	=IF(B8="","",(DATEDIF(B8,TODAY(),"MD")))

 

[awoohaw]

I'm really unsure of your ultimate objective.
But, here are a few ideas... maybe you can work them into what you want...

Book1
	A	B	C	D	E	F
1
2
3
4
5
6
7		Hire In Date	Years	Months	Weeks	Days
8		2008-02-04	15	6		11
9				Counting Mondays	811
10				Days Divided by 7, rounded down	810
11				Weeks Since Last "montheversary"	1	4
12
13
Sheet1

Cell Formulas
Range	Formula
C8	C8	=IF(B8="","",(DATEDIF(B8,TODAY(),"Y")))
D8	D8	=IF(B8="","",(DATEDIF(B8,TODAY(),"YM")))
F8	F8	=IF(B8="","",(DATEDIF(B8,TODAY(),"MD")))
E9	E9	=NETWORKDAYS.INTL(B8,TODAY(),"0111111")
E10	E10	=INT((TODAY()-B8)/7)
E11	E11	=INT((TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(B8)))/7)
F11	F11	=TODAY()- (DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(B8))+7*E11)

 

[Robert Wyatt]

what I'm looking to do is to give how many years, months, weeks, and days I have been employed. I have all about the weeks figured out. what you have shown me isn't quite what I was hoping for.
I well just keep trying to figure it out on my own.

 

[awoohaw]

well, how do you define a year, month, and week?
Is 1 year every anniversary of 2/4/2008? From there how do you calculate a month - is it every 2nd of April (or 4th of February)? and is weeks based up the most recent monthiversary? - If so, I thought I was pretty close to what I have on Row 11 for weeks and days.

What is your expectation for today August 16, 2023?