I need to parse an ISO8601 date/time format with an included timezone (from an external source) in Excel/VBA, to a normal Excel Date. As far as I can tell, Excel XP.
For some reason, Excel forces me to use my region for footers when I insert the date. However, it also doesn't auto update when the worksheet opens. Without the formatting this is a rather trivial issue, however formatting the text is problematic and am not sure how to fix this with all the quotes seemingly needed to be mandatory. Private Sub WorkbookOpen 'you need to get the font to size 6, use calibri, use a light grey #DBDBDB perhaps and use ISO-8601 ActiveSheet.PageSetup.RightFooter = '&'Calibri'&6' & DBDBDB & Format(Now, 'yyyy-mm-dd') End Sub Also now there seems to be a number in the background of the worksheet. Some trying and failing brougt me to this sub, of which you can just paste the relevant bits into your own.
As you can see I have used Chr(34) instead of double quotes to construct the string, because I think that increases readability, that is a personal preference though. Further, you'll notice that I stored the string in a variable, which is not strictly necessary, but which helps when you e.g. Want to use it in a debugging-message, as I do in my sub. I find that in cases such as this it helps to be able to see what the stringlooks like, as it can be kinda hard to keep track of all the quotes, as you already noted.
Iso Date Format
I am not entirely sure where you went wrong in your own construction of the string, though it seems you lack some quotes around the area where you define the font color, as well as the &K which need to precede its hexadecimal value. For reference, I used to help with finding the proper formatting codes for headers and footers. Private Sub test 'you need to get the font to size 6, use calibri, use a light grey #DBDBDB perhaps and use ISO-8601 Dim s As String s = '&' & Chr(34) & 'Calibri' & Chr(34) & ' &6 &KDBDBDB ' & Format(Now, 'yyyy-mm-dd') ActiveSheet.PageSetup.RightFooter = s Debug.Print s End Sub.