VBA to apply custom NumberFormat for date/time time zone

[well2ant]

Hello all,


I apologise if this is a duplicate - I tried to post it earlier but couldn't see it anywhere.

I have written a module to convert date & times provided in UTC time into a different local time zone.
The coding works well and automatically takes into account nay daylight saving time differences that may apply for the selected time zone.
However, so a user can tell what time zone the result is in, I want to apply a custom date format over that cell.


I can manually apply a custom NumberFormat over the cell(s) of
"dd/mm/yyyy hh:mm:ss \(\A\E\S\T \U\T\C\+\1\0\)"
which will make the cell appear like
21/06/2017 16:23:14 (AEST UTC+10)


But since the macro is performing the calculation I would like the macro to apply the correct format at the same time.


NOTE: I do not want to add text to this cell (otherwise I lose the date/time functionality), hence why I'm using a custom format.


I have the following code in my module;

        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

OutPut: is an integer between 0 and 4
I know I probably do not require all the \ symbols in the format, but this ensures the following characters will be literal.


The problem is the value in MyRange.NumberFormat is not being changed, and it will remain as the original value/format it was before the macro was run.


Does anyone have any thoughts?


Thank you in advance.

 

[RoryA]

Is this a macro or a UDF? The latter cannot change the number format of a cell.

 

[well2ant]

Hi Rory,

Yes, it's a UDF.

****! I'll have to have a play around with it then.

Thanks