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

Sam Millican

New Member
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Sam Millican

New Member
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,985

ADVERTISEMENT

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
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 12, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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..

Thanks again for your help

Kr

Sam
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
thanks for the feedback and delighted its working.
 

Watch MrExcel Video

Forum statistics

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