How to format timestamp &D or &T in Excel header/footer?

Question

Does anyone know how to format &D or &T in Excel header or footer? I could use Format(Now, "yyyy/mm/dd HH:nn:ss") to get the format I wanted. But how do i do it with &D or &T?

Answer 1

You can do this using VBA:

With ActiveSheet.PageSetup 
    .RightFooter = "Printed " & Format(Time, "hh:mm") & " - " & Format _ 
    (Date, "mmm dd yyyy") 'Todays Date
End With

Answer 2

As per this Knowledge Base article: "These date and time formats are based on the Date and Time tabs in the Regional Settings Properties dialog box."

So you can change that setting, though that only solves the problem for the computer you're on. The other solution is to use JeffP's macro or a variant thereof.