I have some dates I’m pulling from a database. These dates will go onto a Government report where they want these dates changed to UTC. Then, they want it formatted as “yyyy-mm-ddThh:mm:ss-[UTC offset]”. I do some magic to figure out DST, and the offset needed, and can get it all changed to UTC, and I’m able to format the UTC dates up to the :ss but when I add -06:00 (my current offset) it throws an error.

I’ve tried to format, then take the range.value = range.text & “-06:00” but all I got was ###########-06:00. I’m thinking that I’m potentially going to have to somehow say

dim dateVariable As Date
dim stringVariable As String

dateVariable = range.value
dateVariable.NumberFormat = "yyyy-mm-ddThh:mm:ss"
stringVariable = dateVariable & "-06:00"
range.value = stringVariable

I’m not sure if that makes sense because of how simplified I’ve made it and because of how much it doesn’t work. But somehow I want to take the value, not actually format the cell (which is why I put variable.numberfomat where I’m pretty sure numberformat only works with range which format the cell) because I don’t want the final value to be formatted like that, but do still need the formatting to change the date, then take the formatted value and add my offset and place a string value into the field.