replace date with days between today's date and entered date

[Ev1lZer0]

I know I can do days between formulas, but that requires a minimum of two cells.

one cell to put the date in and and another cell to have this formula in: =IF($O3="",,DAYS($O3,TODAY())*-1)

but I want to try to consolidate this into one cell. If I have to use the VBA editor, I will, but I'd like to steer clear of it.

ideally, the cell would be blank, I would enter a date, such as 1/1/2022 and the cell would compare that date with today's date and give me the number of days between 1/1/22 and today (268 days).

 

[Micron]

IF you hard coded a date in the formula then could use one cell.

ideally, the cell would be blank,

I'd say impossible. A cell that would evaluate its own formula can never be blank at the same time. Same for using vba - you could not type over the function call
(e.g. =myFunction(dates and/or cell references go here). You would have to hard code dates or use other cell references.
If I'm out to lunch, will be happy to be corrected.

 

[Joe4]

Micron is right.
Any Excel formula can either contain a hard-coded value, or a formula, but never both at the same time.
What you want to do can be done, but requires VBA.

Here is VBA code that would do something like that. In order for this to work automatically, it MUST be placed in the proper sheet module.
The easiest way to ensure that is to go the sheet you want to apply it to, right-click on the Sheet Tab Name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if entry is in column E
    If Target.Column = 5 Then
        Application.EnableEvents = False
'       Check to see if entry is a date
        If IsDate(Target) Or IsNumeric(Target) Then
'           Subtract entered date from current date
            Target.Value = Date - Target.Value
'           Format as number
            Target.NumberFormat = "0"
        Else
            Target.ClearContents
            MsgBox "You have not entered a valid date in column E!", vbOKOnly, "ENTRY ERROR!"
        End If
        Application.EnableEvents = True
    End If

End Sub

Note that you will probably need to update this section to limit it to just the area you want this to happen to:

'   Only run if entry is in column E
    If Target.Column = 5 Then