0

Excel VBA issue:

Another person had a problem with the NOW() function because they wanted it to return a static date & time w/o needing to Copy & Paste the value back. I suggested a user-defined function which correctly returns the current date & time in a static manner. The problem is that the NumberFormat property is being ignored & the result that pops out is a number.

I've tried several other formatting solutions, but these are also ignored or result in errors.

Public Function DateNow() As Date

    ActiveCell.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
    DateNow = Now

End Function

Expected result would be a static current date & time. To re-iterate; the code is correct except the part that says ActiveCell.NumberFormat... which is not taking effect.

Edit: I've read the comments below and tried to update it to include a call to a sub, but still no luck. Could someone direct me to the workaround? Thanks.

Public Function DateNow() As Date
    DateNow = Now
    Call DateSub(ActiveCell)
End Function
Public Sub DateSub(rg As Range)

    rg.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"

End Sub
Starnes Student
  • 103
  • 1
  • 8
  • You are not formatting the right cell. Your function will only work if you call it as 'ActiveCell = DateNow'. If you call it as say "A2=DateNow' and A2 is not the 'ActiveCell' than the formatting will apply to the active cell, not to A2. – Michal Rosa May 19 '19 at 23:44
  • A function called as a UDF can only return a value: it can’t format the calling cell – Tim Williams May 20 '19 at 00:21
  • Is this being used as a UDF? If so, writing to a Cell, including formatting, will not work (unless you use one of the work arounds, which can be found on SO). The best option is to apply a format to the cell containing the formula – chris neilsen May 20 '19 at 00:27
  • Sure, please direct me to that workaround if you could. – Starnes Student May 20 '19 at 00:43
  • My go-to workaround for this type of thing does not work for NumberFormat it seems. https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 – Tim Williams May 20 '19 at 05:02

2 Answers2

0
 Sub test()
      ActiveCell = DateNow
      ActiveCell.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
 End Sub


 Public Function DateNow() As Date

     DateNow = Now

 End Function
Michal Rosa
  • 2,459
  • 2
  • 15
  • 28
0

Please try the following since formatting it important so lets format value the within the function it self. Hope! it serves the purpose. In case it helps please mark it accepted answer.

Public Function DateNow() As String

    DateNow = Format(Now, "[$-en-US]m/d/yy h:mm AM/PM;@")

End Function
Kumar Verma
  • 32
  • 6