# Help With A Formula

#### ai1094

##### Board Regular
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### etaf

##### Well-known Member
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
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
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
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"))

You're welcome

#### etaf

##### Well-known Member
just in case others are searching

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

#### MARK858

##### MrExcel MVP
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:

Replies
4
Views
49
Replies
11
Views
230
Replies
4
Views
233
Replies
8
Views
208
Replies
7
Views
121

1,181,598
Messages
5,930,805
Members
436,761
Latest member
mintwaxed

### 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.

### Which adblocker are you using?

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

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