Automatic date

Satish Gupta
Satish Gupta used Ask the Experts™

Need one more help with a simple job.

I want to write a text in excel (e,g, Daily workflow report for 08-Jan-2019). I need that we just need to type  "Daily workflow report for [xxxxxxxxx]" the date should take automatic.
but the issue is that we need one day before of current date (e.g if today is 08-Jan-2019 so it should take 07-Jan-2019 date)
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director of Technology Development, IPM
="Daily Workflow Report for " & TEXT(TODAY()-1,"dd-MMM-yyyy")

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Is it always the cell F9 where you want to have the text "Daily workflow report for" followed by yesterday's date or it can be any cell on the sheet?
In any case you don't even need to type the text "Daily workflow report for", with the code given below you can type anything a number or a letter in F9 and once you hit enter the F9 will be populated with "Daily workflow report for" followed by the yesterday's date.

The following code is placed on "08-Jan-2018" Sheet Module.
To test the code, just type anything in the cell F9 and hit enter.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "F9" Then
    If Target <> "" Then
        Application.EnableEvents = False
        Target.Value = "Daily Report for " & Format(Date - 1, "dd-mmm-yyyy")
        Application.EnableEvents = True
    End If
End If
End Sub

Open in new window


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial