Forum Discussion

Iron Contributor

Mar 20, 2022

Solved

static date update

hi everyone, i have been making data for my company, therefore i need help as follows i want date automatically update in cell b2 as someone input in cell c2, though the date should be static. da...

excel

Excel for web

Formulas and Functions

office 365

HansVogelaar
Mar 22, 2022
harshulz

Select File > Options > Formulas.

Tick the check box "Enable iterative calculation" and set "Maximum iterations" to 1, then click OK.

Select the range where you want the date, e.g. B2:B100.

Enter the formula =IF(C2<>"",IF(B2<>"",B2,NOW()),"") and confirm with Ctrl+Enter to populate the entire selection.

This will enter the date and time; if you only want the date, use TODAY() instead of NOW().

harshulz

Iron Contributor

Mar 22, 2022

thanks hans, but can we formulate it without inserting macro or vba?

HansVogelaar

MVP

Mar 22, 2022

harshulz

Select File > Options > Formulas.

Tick the check box "Enable iterative calculation" and set "Maximum iterations" to 1, then click OK.

Select the range where you want the date, e.g. B2:B100.

Enter the formula =IF(C2<>"",IF(B2<>"",B2,NOW()),"") and confirm with Ctrl+Enter to populate the entire selection.

This will enter the date and time; if you only want the date, use TODAY() instead of NOW().

harshulz
Iron Contributor
Mar 22, 2022
yep this formula works, just waiting for day to end.
but i wonder u made this iteration to 1. what does actually this meant ?
- HansVogelaar
  MVP
  Mar 22, 2022
  harshulz
  
  If you enable iterative calculations, you allow circular references: formulas that refer to the cell containing the formula. This might cause repeated calculations, causing slow performance. By setting the maximum number of iterations to 1, you limit this.