# Freeze today function by entering date in another column (non-VB solution?)

#### Sam Millican

##### New Member
Hi All

I have been trying to think of a solution to my problem with only basic knowledge of Excel, and unsurprisingly have come up short.

My spreadsheet is tracking patients against a treatment target date, I am using the formula =K3-TODAY() (K3 being the treatment target date) so the spreadsheet automatically updates counting down the days to said date.

Essentially, I am looking to freeze this function once I enter a date in another cell (i.e. date drugs were administered) so that the days remaining to treatment target date is locked and will no longer update.

Any help would be greatly appreciated!

KR

Sam

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### pjmorris

##### Well-known Member
if we assume that date entered in another cell (you don't say where it is on the sheet) is the date used for the calculation then try this:

Excel Formula:
``=K3-IF(M3<>"",M3,TODAY())``
assuming that M3 is the cell that contains the date that you want frozen.

HTH

#### Sam Millican

##### New Member
Hi Peter

I have entered the formula and will re-check the sheet post-midnight to see if it has frozen the countdown.

Bw

Sam

#### Sam Millican

##### New Member
if we assume that date entered in another cell (you don't say where it is on the sheet) is the date used for the calculation then try this:

=K3-IF(M3<>"",M3,TODAY()) assuming that M3 is the cell that contains the date that you want frozen.

HTH
Hi Peter

The formula above does alter the number of days remaining based on the new date entered (which is something I want), but unfortunately has not halted the countdown / today function.

Would you have any other solutions / ideas to solve?

Many thanks

Sam

#### mikerickson

##### MrExcel MVP

TODAY() will not stop re-calculating. At the very least it will be triggered by the automatic calculation that happens when opening or closing a workbook.

But @pjmorris formula shouldn't change its value as a result of that calculation.

Are you using ctrl-; to enter todays date in M3 as a constant, or are you putting =TODAY() in M3 ?

#### Sam Millican

##### New Member
TODAY() will not stop re-calculating. At the very least it will be triggered by the automatic calculation that happens when opening or closing a workbook.

But @pjmorris formula shouldn't change its value as a result of that calculation.

Are you using ctrl-; to enter todays date in M3 as a constant, or are you putting =TODAY() in M3 ?
Hi Mike

Ah, well that would explain it.

I haven't been using ctrl-; I have only been inputting date normally into cell (M3). For clarity, please see an example of the sheet below:

 Target Date (K3) Days Remaining Until Target Date (L3) Pathway end date (M3) 15/03/21 =K3-TODAY() Date to 'stop' L3 / show how many days remain to target date

#### pjmorris

##### Well-known Member

HI Sam, the key bit about Mike's comment was the distinction between entering the date using CTRL-; or type '=TODAY()' into the cell. TODAY is a function which updates automatically.

It depends on exactly what you mean by 'inputting date normally'; you can just type '15/3/2021' or 15 Mar 2021 or press CTRL=; which will all work just as well as one another.

HTH

#### Sam Millican

##### New Member
HI Sam, the key bit about Mike's comment was the distinction between entering the date using CTRL-; or type '=TODAY()' into the cell. TODAY is a function which updates automatically.

It depends on exactly what you mean by 'inputting date normally'; you can just type '15/3/2021' or 15 Mar 2021 or press CTRL=; which will all work just as well as one another.

HTH

Hi Peter

Yes exactly, just inputting a date using any method to indicate the definitive treatment date and stop the today function.

It sounds like it will be easier to manually input the days remaining based on the pathway end date, rather than relying on a formula in this case.

Thanks for your help with this, it's much appreciated.

All the best

Sam

#### Sam Millican

##### New Member
if we assume that date entered in another cell (you don't say where it is on the sheet) is the date used for the calculation then try this:

Excel Formula:
``=K3-IF(M3<>"",M3,TODAY())``
assuming that M3 is the cell that contains the date that you want frozen.

HTH

Hi Peter

Not sure what happened before, but this seems to be working exactly as it should now! User error no doubt..

Kr

Sam

#### pjmorris

##### Well-known Member
thanks for the feedback and delighted its working.

Replies
2
Views
207
Replies
10
Views
539
Replies
5
Views
358
Replies
31
Views
2K
Replies
1
Views
2K

1,129,845
Messages
5,638,682
Members
417,042
Latest member
kshipp91

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