Excel numberformat property

QuestionEdit

QUESTION: i have written a macro to convert local time to UTC time. This will be used as a function in excel. The conversion happens without any issues. Now i am trying to convert the cell format (which contains the converted value) to date time format. This is something that is not working for me. Would you able to check and help me out with this?

My code is as below

Public Function utc(dt As Date) As Date

   Application.Volatile
   od = dt
   Set oShell = CreateObject("WScript.Shell")
   atb = "HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
   offsetMin = oShell.RegRead(atb)
   nd = (DateAdd("n", offsetMin, od))
   ActiveCell.NumberFormat = "m/d/yyyy h:mm"
   utc = nd

End Function

ANSWER: Well, WHAT exactly is not working? What is NOT doing that you want it to do?

---------- FOLLOW-UP ----------

QUESTION: Sorry for not being clear.

The following line is not working.

ActiveCell.NumberFormat = "m/d/yyyy h:mm"

The DateAdd function gives me the result in decimal format. WIth the above function, i am trying to change the cell holding my result to "m/d/yyyy h:mm" format so the date is human readable. And this is what is not working

Hope I am clear with this explanation.

Imran

ANSWER: Is the activecell not being formatted correctly or is the date-time you are putting in the activecell NOT the actual desired date-time? I don't see any code that actually puts nd in the activecell. Try adding msgbox nd AFTER nd = (DateAdd("n", offsetMin, od)) to see what nd is.

I tried to code here and it worked when I put nd in the cell. I used 1 for offsetMin and just used Date() for dt and thus eliminated this

 od = dt
  Set oShell = CreateObject("WScript.Shell")
  atb = "HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
  offsetMin = oShell.RegRead(atb)

when I tested

---------- FOLLOW-UP ----------

QUESTION: msgbox does show the result in correct "m/d/yyyy h:mm" format, but the data in the cell doesn't come up in "m/d/yyyy h:mm" but stays in General format.

Here is how it (should) work: 1. Cell A1 would contain a date in the format: "m/d/yyyy h:mm" 2. Now I select Cell B1 (which is now the active cell) and i call my user defined function utc(A1) 3. I expect the date in B1 to be of the format "m/d/yyyy h:mm" which is not the case for me.

Public Function utc(dt As Date) As Date

  Application.Volatile
  offsetMin = 1   
  nd = (DateAdd("n", offsetMin, dt))
  ActiveCell.NumberFormat = "m/d/yyyy h:mm"
  utc = nd

End Function

Imran

AnswerEdit

Not sure why this is happening. You might try formatting the result BEFORE putting the result in the cell. For example, something like:

nd = Format((DateAdd("n", offsetMin, dt)), "m/d/yyyy h:mm"

but not sure if that will work either. If not then I don't know why and you may want to try another volunteer

Followup - You can't apply formatting to a cell in a UDF