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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0
just in case others are searching

=IF ( INT((TODAY()-D2)/7) > 4 , 5 , INT((TODAY()-D2)/7) )
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top