Help With A Formula

ai1094

Board Regular
Joined
Aug 23, 2018
Messages
90
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,901
Office Version
365
Platform
MacOS
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,185
Office Version
365, 2010
Platform
Windows, Mobile
One really ugly way...

MyTest Bday.xlsb
DE
215/07/20202 Weeks
309/06/2020Over 4 weeks
425/06/2020Over 4 weeks
511/07/20203 Weeks
621/06/2020Over 4 weeks
731/07/20200 Weeks
807/07/20203 Weeks
919/07/20202 Weeks
1029/06/20204 Weeks
1117/06/2020Over 4 weeks
1201/06/2020Over 4 weeks
1327/07/20200 Weeks
1413/06/2020Over 4 weeks
1503/07/20204 Weeks
1623/07/20201 Week
1705/06/2020Over 4 weeks
Sheet3
Cell Formulas
RangeFormula
E2:E17E2=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

Board Regular
Joined
Aug 23, 2018
Messages
90
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

Board Regular
Joined
Aug 23, 2018
Messages
90
It may be ugly, but it works! Thanks I think this will suffice.

One really ugly way...

MyTest Bday.xlsb
DE
215/07/20202 Weeks
309/06/2020Over 4 weeks
425/06/2020Over 4 weeks
511/07/20203 Weeks
621/06/2020Over 4 weeks
731/07/20200 Weeks
807/07/20203 Weeks
919/07/20202 Weeks
1029/06/20204 Weeks
1117/06/2020Over 4 weeks
1201/06/2020Over 4 weeks
1327/07/20200 Weeks
1413/06/2020Over 4 weeks
1503/07/20204 Weeks
1623/07/20201 Week
1705/06/2020Over 4 weeks
Sheet3
Cell Formulas
RangeFormula
E2:E17E2=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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,185
Office Version
365, 2010
Platform
Windows, Mobile
You're welcome
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,901
Office Version
365
Platform
MacOS
just in case others are searching

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,185
Office Version
365, 2010
Platform
Windows, Mobile
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
DEF
511/07/20203 Weeks2
919/07/20202 Weeks1
Sheet3
Cell Formulas
RangeFormula
E5,E9E5=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,F9F5=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.
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,184
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top