Excel dates in a specific format

[henry1972]

I'd like to find a way to automatically be able to format dates as yyyy-mm-dd hh:mm:ss without having to keep retyping that in the Format Cells -> Number -> Custom section.

Is there a) any way to automatically be able to add this as one of the options so I can simply select it or b) is there any other way that I can create the ability to format dates like this with one click?

Many thanks in advance for any help you can give.

 

[RickXL]

Hi,

I got fed up with constantly adding seconds to the formats so I came up with this:

Sub ReplaceDateTimeFormats()

   Static Action As Long
   Dim tmpFormat As String
   
   ' First time round ...
   If Action = 0 Then
      ' Create all the formats that will be needed
      With Range("A1")
         tmpFormat = .NumberFormat
         .NumberFormat = "dd/mm/yyyy hh:mm:ss"
         .NumberFormat = "yyyy/mm/dd hh:mm:ss"
         .NumberFormat = "m/d/yyyy h:mm"
         .NumberFormat = tmpFormat
      End With
   End If
   
   ' Increment the action number
   Action = (Action Mod 3) + 1

   ' Set up the formats for the action
   With Application
      Select Case Action
         Case 1
            .FindFormat.NumberFormat = "m/d/yyyy h:mm"
            .ReplaceFormat.NumberFormat = "dd/mm/yyyy hh:mm:ss"
         Case 2
            .FindFormat.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            .ReplaceFormat.NumberFormat = "yyyy/mm/dd hh:mm:ss"
         Case 3
            .FindFormat.NumberFormat = "yyyy/mm/dd hh:mm:ss"
            .ReplaceFormat.NumberFormat = "m/d/yyyy h:mm"
      End Select
   End With
   
   ' Process the action
   Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
   
End Sub

I linked the code to a button on my Quick Access Toolbar.
There is a cycle of three formats and successive click goes round each one in turn.
It uses the standard Find/Replace feature of Excel to change the Formats in the whole worksheet.
First time round it adds the formats it wants to the existing list.

The final version resides in my Personal Workbook and I seem to think that I needed to self-certify the code to prevent the macro warnings from appearing.
If it looks promising we could work on the details. I note that my preferred formats use slashes, for instance.
Also, the m/d/yyyy format is not as it seems. It means d/m/yyyy - I think that is an Excel quirk.

Regards,

 

[Natep]

This small sample will change the format for column A when run.

Change A:A to whatever column you wish to format

ALT+F11 to open VBA, file New Module

Sub DateTime()
'
' Set DateTime Format
'
    Columns("A:A").Select
    Selection.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub

ALT+F8 to open Macro selection, select and run DateTime to change Column A to the format you desired

Alternatively, you can comment out the 1st line of code and add the Macro to your Quick Access toolbar or a Ribbon and run it only on the selected cells.

 

[henry1972]

Thank you RickXL and Natep for responding to my post. You're both life savers; it drives me mad repeating the same processes again and again! Hope you both have a good weekend. Thanks again.

 

[RickXL]

You're welcome.

... it drives me mad repeating the same processes again and again!

And I know just what you mean.

Regards,