VBA for Excel misbehaving... doesn't return the right value

0
votes

I have the following function in my worksheet...

Format(Range("AG1"), "mm")

I'm using it to auto-populate the saveas filename, which is a larger function, but it all works except for this part. As part of that larger function there is

Year(Range("AG1"))

which is returning the correct year in "yyyy" format. (It doesn't require any specific formatting, it just returns the year in 4-digit format.)

However, the first function, Format(Range("AG1"), "mm") is always returning 01, indicating that is is January, which it isn't always. I need the month based on the current date to be returned in "mm" format.

The Range("AG1") refers to a cell in the worksheet that has a date entered as [mm/dd/yyyy].

Any ideas what I'm doing wrong?

2
vbaexcel
Are you using English/American/etc regional settings, or something else? (I think some of the other regional versions of Excel, use something other than "mm" for month) - YowE3K
The day part of your data in AG1 wouldn't happen to be 1 by any chance? - DeanOC
If you put 04/02/2017 into AG1, does your function now return 2? And is your locale set to use "dd/mm/yyyy" format? If you have text in the cell, and Excel can interpret it as a valid dd/mm/yyyy date, it will. If it can't, it will try to interpret it as a mm/dd/yyyy date. - YowE3K
... and yet it is returning 1 as the month ... curious. Edit your question to include several examples of dates; both with amigous day/months and without. Show the results you are receiving and state what you want to receive. - user4039065
Just for the record.... it is a little late for an April Fool's joke. - user4039065

2 Answers

0
votes

According to this table you should be using "m"

No. Formatting code Display as      Example
1   ddd             Weekday name    Sat
2   dddd            Weekday name    Saturday
3   mmm             Month  name     Jan
4   mmmm            Month name      January
5   m               Month           1
6   d               Day             17
0
votes

Here's the final code that worked for me...

Private Sub btnCreate_Click()
  ActiveSheet.Copy
  Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
  ActiveSheet.Shapes.Range(Array("btnCreate")).Select
  Selection.Delete
  ActiveWorkbook.SaveAs "C:\Users\" & (Environ$("Username")) & "\Desktop\" & "Timecard, " & Year(Range("AG1")) & "-" & Format(Range("AG1"), "mm") & " - " & _
    Range("R2").Value & ", " & Range("T2").Value, xlOpenXMLWorkbookMacroEnabled
  ActiveWindow.Close
End Sub