Help With A Formula

ai1094

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,761
Office Version
  1. 365
Platform
  1. 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
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. 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
92
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
92

ADVERTISEMENT

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
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,761
Office Version
  1. 365
Platform
  1. 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
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. 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:

Watch MrExcel Video

Forum statistics

Threads
1,133,322
Messages
5,658,149
Members
418,430
Latest member
Kizega

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top