Help With A Formula

[ai1094]

Hi, I have a column (D) in my workbook that shows when a file was last updated. I need help with a formula (or whatever approach is right) to determine how long it has been since it's last update.

I would like to use the current date comparing against what the date is in the column it was last modified. Please let me know if you need more detail.

Essentially I am trying to group it like so:

1 Week
2 Weeks
3 Weeks
4 Weeks
Over 4 weeks

 

[etaf]

today() will give you todays date


=Today() - D2
will give you the number of days

then you could use
=INT((TODAY()-D2)/7)
so less than 1 week would be 0
1 week to 2 weeks would be 1
etc

 

[MARK858]

One really ugly way...

MyTest Bday.xlsb
	D	E
2	15/07/2020	2 Weeks
3	09/06/2020	Over 4 weeks
4	25/06/2020	Over 4 weeks
5	11/07/2020	3 Weeks
6	21/06/2020	Over 4 weeks
7	31/07/2020	0 Weeks
8	07/07/2020	3 Weeks
9	19/07/2020	2 Weeks
10	29/06/2020	4 Weeks
11	17/06/2020	Over 4 weeks
12	01/06/2020	Over 4 weeks
13	27/07/2020	0 Weeks
14	13/06/2020	Over 4 weeks
15	03/07/2020	4 Weeks
16	23/07/2020	1 Week
17	05/06/2020	Over 4 weeks
Sheet3

Cell Formulas
Range	Formula
E2:E17	E2	=IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))>4,"Over 4 weeks",IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))=1,(ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))&" Week",ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2)&" Weeks"))

 

[ai1094]

Thanks, but is there anyway to tag them? Because anything over 4 weeks I want to group together instead of showing 5 weeks, 6 weeks, etc.

today() will give you todays date


=Today() - D2
will give you the number of days

then you could use
=INT((TODAY()-D2)/7)
so less than 1 week would be 0
1 week to 2 weeks would be 1
etc

 

[ai1094]

It may be ugly, but it works! Thanks I think this will suffice.

One really ugly way...

MyTest Bday.xlsb
	D	E
2	15/07/2020	2 Weeks
3	09/06/2020	Over 4 weeks
4	25/06/2020	Over 4 weeks
5	11/07/2020	3 Weeks
6	21/06/2020	Over 4 weeks
7	31/07/2020	0 Weeks
8	07/07/2020	3 Weeks
9	19/07/2020	2 Weeks
10	29/06/2020	4 Weeks
11	17/06/2020	Over 4 weeks
12	01/06/2020	Over 4 weeks
13	27/07/2020	0 Weeks
14	13/06/2020	Over 4 weeks
15	03/07/2020	4 Weeks
16	23/07/2020	1 Week
17	05/06/2020	Over 4 weeks
Sheet3

Cell Formulas
Range	Formula
E2:E17	E2	=IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))>4,"Over 4 weeks",IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))=1,(ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2))&" Week",ISOWEEKNUM(TODAY())-ISOWEEKNUM(D2)&" Weeks"))

 

[MARK858]

You're welcome

 

[etaf]

just in case others are searching

=IF ( INT((TODAY()-D2)/7) > 4 , 5 , INT((TODAY()-D2)/7) )

 

[MARK858]

just in case others are searching

=IF(INT((TODAY()-D2)/7)>4 ,5 ,INT((TODAY()-D2)/7) )

Depends what the OP is treating a week as i.e. periods of 7 calendar days from today or a working week for instance the results below differ...

MyTest Bday.xlsb
	D	E	F
5	11/07/2020	3 Weeks	2
9	19/07/2020	2 Weeks	1
Sheet3

Cell Formulas
Range	Formula
E5,E9	E5	=IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D5))>4,"Over 4 weeks",IF((ISOWEEKNUM(TODAY())-ISOWEEKNUM(D5))=1,(ISOWEEKNUM(TODAY())-ISOWEEKNUM(D5))&" Week",ISOWEEKNUM(TODAY())-ISOWEEKNUM(D5)&" Weeks"))
F5,F9	F5	=IF( INT((TODAY()-D5)/7)>4,5,INT((TODAY()-D5)/7) )

Edit:
Possibly for my formula I should have used WEEKNUM(***,1) rather than ISOWEEKNUM(***) so it starts on a Sunday rather than a Monday.