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

[Sam Millican]

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

 

[pjmorris]

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

 

[Sam Millican]

Hi Peter

Many thanks for your quick reply.

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]

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]

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]

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

Thanks for your reply.

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]

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]

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]

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

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

Thanks again for your help

Kr

Sam

 

[pjmorris]

thanks for the feedback and delighted its working.