well2ant
New Member
- Joined
- Dec 16, 2014
- Messages
- 12
Hello all,
I'm writing a module to convert date & time provided in UTC format, into a local time zone of my choice.
The code works fine in converting to the required time zone, but I want to them place a formatting over the top to indicate which time zone it relates to.
I do not want to add text to the field, hence why I'm using a format.
If I select the cell(s) I can manually apply a custom number format of
"dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
which makes the date/time appear as
21/06/2016 21:16:03 (AEDT UTC+11)
However, I'm trying to get the macro to apply the appropriate format of the time zone that has been calculated. I am using the code below, but it does not change the value in MyRange.NumberFormat from what it was before the macro is run.
OutPut: is an integer 0 to 4.
I realise the I do not need all the \ characters, but it ensures that each character is literal.
Does anyone have any suggestions?
Thank you in advance.
I'm writing a module to convert date & time provided in UTC format, into a local time zone of my choice.
The code works fine in converting to the required time zone, but I want to them place a formatting over the top to indicate which time zone it relates to.
I do not want to add text to the field, hence why I'm using a format.
If I select the cell(s) I can manually apply a custom number format of
"dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
which makes the date/time appear as
21/06/2016 21:16:03 (AEDT UTC+11)
However, I'm trying to get the macro to apply the appropriate format of the time zone that has been calculated. I am using the code below, but it does not change the value in MyRange.NumberFormat from what it was before the macro is run.
Code:
Select Case OutPut
Case AEDT_Format
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\D\T \U\T\C\+\1\1\)"
Case AEST_Format
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\E\S\T \U\T\C\+\1\0\)"
Case ACDT_Format
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\D\T \U\T\C\+\1\0\.\5\)"
Case ACST_Format
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\C\S\T \U\T\C\+\9\.\5\)"
Case AWST_Format
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss \(\A\W\S\T \U\T\C\+\8\)"
Case Else
MyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End Select
End With
OutPut: is an integer 0 to 4.
I realise the I do not need all the \ characters, but it ensures that each character is literal.
Does anyone have any suggestions?
Thank you in advance.