Nearest Monday

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
Hello.

Im using this formula:

=TODAY()+(7-WEEKDAY(TODAY(),2)+1)

To return the date of the next monday after today, which works fine.

Is there a way to make sure that the date "locks" in place when the next monday is reached?

For example, today is the 11th, so the formula gives the 13th, which is the next monday. However, on the 14th, the formula will show the next monday again. Is there a way to lock the cell to the 13th when the 13th is reached?

Hope that makes sense

Thanks
Jon
 

Some videos you may like

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

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
jondallimore,

Unless you are prepared to intervene manually to convert the formula to a hard value or you create the next Monday date using vba then you are stuck with the formula recalculating and providing the next Monday.
Do you have any other dates that are to be input manually that can be used as a reference in your next Monday formula?
 

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
no. I want to avoid entering the dates manually.
Is there a way to enter the date in a cell each time I save the file or something like that? This is a file Im using as a template, so I just save it as a new file each week.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
no. I want to avoid entering the dates manually.
Is there a way to enter the date in a cell each time I save the file or something like that? This is a file Im using as a template, so I just save it as a new file each week.

Since you are saving this as a new file each week, can I assume you have the template's starting date in some cell somewhere (if not, could you put it in there somewhere)? If so, just change the TODAY() function calls in your formula to a cell reference to that cell with the starting date in it. When calculating then next Monday, it does not matter which date in the current week you use... the date does not have to be a dynamic "today" date, any date in the week works no matter what the current date is.
 

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
Yep - which is why it would be good to have a cell which displays the date the file was first saved on. That way, it would automatically update when the file is saved, but not each time the file is opened. Is there a way to do that?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
Yep - which is why it would be good to have a cell which displays the date the file was first saved on. That way, it would automatically update when the file is saved, but not each time the file is opened. Is there a way to do that?
You could with VBA code, but not with straight Excel formulas, but my point was slightly different. I figured since this was a weekly template and that you manually did something with it weekly, that you are already inserting a date for that week into the workbook somewhere now and that you could simply point your formula to it instead of using the TODAY() function. However, if you do not already have such a date cell, create one and enter the date manually (you are already doing something to the template now, so how hard would it be to insert a date). Perhaps you are not aware of the shortcut for entering today's date as a constant (it's quite simple)... select the cell, press the CTRL key along with the semi-colon key (;) and while you are not interested in the time at this point, there is a shortcut for it as well... CTRL+SHIFT and the semi-colon key.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,200
Members
414,513
Latest member
junbuggle

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