On 1st sheet, when date field doesn't contain any number (i.e. date, as date is a number in Excel), then user can enter/select only current date into date field. When date field contains a number which is >0, then only allowed entry will be red-colored warning text - and user can press Esc to cancel the entry, or select the warning text and after that overwrite it with current date.
2nd sheet is helper sheet, where validation list value (current date or warning text) is calculated. This sheet is meant to be at least Hidden, but Very Hidden (the sheet is not shown when user tries unhide sheets) is best option.
|