[RESOLVED] VBA swaps month and day pre the 13th day of the month

[Camel11]

Hey guys,

It is me again. I have written a macro that makes the life of certain people easier who does not need to open / save as / template report files every day.
What I done is a macro that opens blank file, saves it in certain directory (picked by the agent) as it takes the name / today's date and the words "daily report " in order to save the file.

Everything runs great except for this annoying error. VBA keeps swapping the date/ month of today's date. If it is supposed to show 08/09, it will show 09/08 as today's date in the fresh saved report. I tried everything to solve this but I failed.

This is part of the code:

Dim Reportdate As Date
Reportdate = Range("F4") 'this cell equals the formula = today()

then the code opens new blank file (I have skipped the code as it is working and will only waste your time)

Range("B2") = Reportdate 'this is range in the activeworkbook,activesheet
Range("B2").NumberFormat = "DD/mm/YY"
Range("B2").HorizontalAlignment = xlRight 'this all works fine except for the date swap.

Please can you help with this?

[vbfbryce]

Try this and see if cell A2 has today's date in the format you're expecting:

Sub dates()
    Dim ws As Worksheet
    Dim dt As Date
    Dim dt2 As String
    
    Set ws = ActiveSheet
    dt = Now()
    Stop
    dt2 = Format(dt, "dd/mm/yy")
    ws.Range("a1").Value = dt
    ws.Range("a2").Value = dt2
    Set ws = Nothing
End Sub

[westconn1]

with bryce's code, try
ws.Range("a2").Value = DateValue(dt)

or just
ws.range("a2").value = date

[TnTinMN]

What you are describing as happening should not happen based on the code that you have shown us.

VBA swaps month and day pre the 13th day of the month

Your thread title indicates that the behavior is date dependent. This indicates to me that there is something in the code that you have not shown that is making the change.

Can you make a sample workbook and template package that reproduces this error and upload it to the forum? Put the files in a ZIP file first.

[Camel11]

Hi,

Well it is quite simple. We have a file which is the macro file (a hidden cell value in it = today()).

What the macro does is simply opens a template report, saves it using today & the name of the agent (there is a list menu where they pick it again on the macro file) and the words " daily report".

Then after it saves it it populates a certain cell in the freshly saved report which equals the cell in the macro file (=today() one). It all works fine except for the fact that the cell in the freshly saved report is not in the way I want it to be. The worst part of it is that it ALL works fine on my machine but it fails to do it on other computers...

Here is a part of the code:

'Please ignore some of the variables as they are intented for different version of this macro (which copies and pastes pending cases from the previous file, however, with or without it, the problem stays the same.

As today is the worst day for testing I will test the provided code (thank you for it Bryce and Westconn), tomorrow.

Sub Processreports()

'Main variables

Dim Mothermacro As String
Mothermacro = Range("G2")
Dim Reportdate As Date
Reportdate = Range("F4")
Dim Savepath As String
Savepath = Range("J2")
Dim Filepath As String
Filepath = Range("A2")
Dim Agentname As String
Agentname = Range("H2")
Dim Data As String
Data = Range("F2")
Dim Newfilepath As String
Newfilepath = Range("D2")
Dim WB As Workbook
Set WB = ThisWorkbook
Dim Yesterday As String
Yesterday = Range("L2").Value
Dim AfterYesterday As String
AfterYesterday = Range("N2").Value

Workbooks.Application.ScreenUpdating = False


'Open old file
On Error GoTo Openoldfileerror
    Workbooks.Open Filepath
On Error GoTo 0
    Dim Oldfile As Workbook
    Set Oldfile = ActiveWorkbook    

'Add date and allign to the right

Range("B2") = Reportdate
Range("B2").NumberFormat = "DD/mm/YY"
Range("b2").HorizontalAlignment = xlRight

'Save the new file
Data = Day(Data) & "." & Month(Data) & "." & Year(Data)
    On Error GoTo SavenewfileError
ActiveWorkbook.SaveAs Filename:=Savepath & Agentname & " Daily report " & Data & ".xlsx"
    On Error GoTo 0

.......

[vbfbryce]

check to see if the "region and language" options are different on the various computers

[Camel11]

check to see if the "region and language" options are different on the various computers

I have just checked. They are the same as mine.

[Zvoni]

You do realize, that you use the variable "Data" to build your Save-FileName, and that Data gets its value from range("F2")?
What's the Value in F2?

[Camel11]

just ignore the "Data" variable as it is working fine. The macro will never swap the places of the date and month when it saves the file. The rage "F4" is declared as reportdate and it is =today(). It shows the correct date, however when line range B2 = report date is executed it swaps them out of nowhere.

[westconn1]

it can happen here if the date is a string variable, but i tested your code, works correctly on today's date

Dim mydate As Date
mydate = Range("d16")
Range("b16") = mydate
Range("B16").NumberFormat = "DD/mm/YY"
Range("b16").HorizontalAlignment = xlRight

=today() in D16

[Camel11]

Hi again. I managed to find an easy solution. I simply used =today() function in the blank template file on the required field and then used

range('required field").value = range('required field").value so that I can remove the formula and it all works fine. There is no need of copying or pasting or = other ranges.
Thank you very much for your help. I have to use date functions a lot at my position so I will most likely use formulas you provided in my future work if this happens again in a more complicated code.