Worksheet tabs to include date from a cell

A Worksheet Change: Rename Sheet

The Worksheet Change event is triggered when you 'manually' change a cell. This includes the following: 
 - After entering edit mode, i.e., using F2 or double-clicking a cell to modify it, but only after confirmation (pressing Enter). This doesn't necessarily mean the value was modified. Also, if you exit edit mode with the ESC key, the event is not triggered.
 - After cut/copy-pasting.
 - After writing to cells using VBA.
In your particular case, the event will only trigger when you change the value in Sheet1, since Sheet3 has a formula that will not trigger the event.
Therefore, you need to use the code only for Sheet1 and update both sheet names when this change happens.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim targetCell As Range: Set targetCell = Me.Range("H1")
    If Intersect(targetCell, Target) Is Nothing Then Exit Sub
    
    Dim sDate As String: sDate = Format(targetCell.Value, "mmm-dd-yyyy")
    
    Me.Name = "AR - as of " & sDate
    Sheet3.Name = "AP - as of " & sDate

End Sub

Thank you!!  There is one minor issue; cell H1 on tab 3 is linked to cell H1 on tab 1, so when I update the date in H1 on tab 1, cell H1 on tab 3 also updates.  When the date changes in H1 on tab 1, the date in the tab name updates as it should, but the tab name on tab 3 does NOT update.  I would like the tab names on both tabs 1 and 3 to update when I change the date in cell H1 on tab 1.  Thank you for your assistance.    

You can do this with a simple VBA macro, since sheet names can’t be linked directly to cells. Add this code to the relevant sheets in the VBA editor (Alt+F11):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H1")) Is Nothing Then
        Dim dt As String
        dt = Format(Me.Range("H1").Value, "dd-mmm-yyyy")
        
        Select Case Me.CodeName
            Case "Sheet1"   ' adjust to your sheet code name
                Me.Name = "AR - as of " & dt
            Case "Sheet3"
                Me.Name = "AP - as of " & dt
        End Select
    End If
End Sub

Whenever H1 changes, the tab name updates automatically.

https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/name-a-worksheet-by-using-a-cell-value

Sample available at AutoRenameWorksheet.xlsm (file to download - won't work with Excel Wed/Online)

NB: Not a VBA expert at all

You will need to use VBA coding to achieve this.