Excel does not recognize mm/dd/yyyy format date.

[yxz152830]

Gurus,
This is actually a 2 part question:
1. below code gives me this:

09-05-2022​

record_range(rows_total + rows_count, 5).NumberFormat = "mm/dd/yyyy"

instead of 09/25/2022

2. when i manually enter 09/25/2022, I cannot change the format using custom/long/short date.

This is really confusing. Could someone please help? Thanks!

 

[6StringJazzer]

What data is in the cells? If you have text in that cell that is "09-05-2022" it doesn't matter what format you set, it is always going to look like "09-05-2022". That is my guess for what is going on. Check to see whether you have text vs. actual date values.

I am not following what you are describing in #2. What does it look like as soon as you type it in? What happens if you try to change the format?

 

[Joe4]

Your code looks a little confusing.
"record_range" is not a valid range call.
Can you show us the entire block of code, where we can see all variable declarations and how they are being set?

Note that if you make a date entry into a cell, and changing the format on that cell does not change how that date is presented, it usually means you entered the date as text instead of an actual date.
If your the Number format on the cell is set to "Text" at the time of data entry, changing the format will not effect it. It needs to be changed before data entry, or you need to re-enter it afterwards (or run some VBA code it).

 

[yxz152830]

Your code looks a little confusing.
"record_range" is not a valid range call.
Can you show us the entire block of code, where we can see all variable declarations and how they are being set?

Note that if you make a date entry into a cell, and changing the format on that cell does not change how that date is presented, it usually means you entered the date as text instead of an actual date.
If your the Number format on the cell is set to "Text" at the time of data entry, changing the format will not effect it. It needs to be changed before data entry, or you need to re-enter it afterwards (or run some VBA code it).

What I was doing is really just this:
cells(1,2)=cells(1,3).value
cells(1,2).NumberFormat = "mm/dd/yyyy"

cells(1,3) is where I enter my input. I type "09/25/2022" but the output is "09-25-2022"

 

[Joe4]

I cannot reproduce that behavior.

When are you making the data entry and when/how are you running this VBA code?

 

[yxz152830]

I cannot reproduce that behavior.

When are you making the data entry and when/how are you running this VBA code?

So same code gives you different results? Did the code give you exactly the format mm/dd/yyyy?
I ran the code normally like just hitting F5 and entered 09/25/2022 in the cell that I was gonna copy

 

[Joe4]

F5? F5 is the "Go To" keyboard shortcut. It has nothing to do with VBA.
Can you post your ENTIRE VBA procedure, exactly as you have it, and walk us through the exact steps in the exact order you are doing them?

Also, can you let us know what your Regional Settings are for date in Excel?
Different areas of the world use different Regional Settings.

 

[6StringJazzer]

F5? F5 is the "Go To" keyboard shortcut. It has nothing to do with VBA.

In the VBA editor, F5 runs the selected Sub.

 

[Alex Blakenburg]

What happens if you do this:

Cells(1, 2).NumberFormat = "mm/dd/yyyy"
Cells(1, 2) = DateValue(Cells(1, 3).Value)

 

[yxz152830]

F5? F5 is the "Go To" keyboard shortcut. It has nothing to do with VBA.
Can you post your ENTIRE VBA procedure, exactly as you have it, and walk us through the exact steps in the exact order you are doing them?

Also, can you let us know what your Regional Settings are for date in Excel?
Different areas of the world use different Regional Settings.

Hi I meant I F5 to run the code.
If it makes any difference, my regional setting is Canadian I think.

Sub maintenance_entry()
Dim data_range, cell, record_range As Range
Dim record_row, rows_count As Integer
Set data_range = Range(Cells(2, 2), Cells(11, 2))
Set record_range = Sheets("Maintenance Record").ListObjects("maintenance").DataBodyRange
rows_total = record_range.Rows.Count
rows_count = 1
If Cells(1, 6).Value = "" Or Cells(1, 2).Value = "" Then
MsgBox ("Enter machine name, enter date")
Exit Sub
End If

For Each cell In data_range
If IsEmpty(cell) = False Then
record_range(rows_total + rows_count, 2) = cell.Offset(0, -1)
record_range(rows_total + rows_count, 4) = Cells(1, 6).Value
record_range(rows_total + rows_count, 6) = cell.Value
record_range(rows_total + rows_count, 5) = Cells(1, 2).Value
record_range(rows_total + rows_count, 5).NumberFormat = "mm/dd/yyyy"
    If cell.Value = "Replace" Then
    record_range(rows_total + rows_count, 3) = 1
    End If
record_range(rows_total + rows_count, 7) = cell.Offset(0, 1).Value
record_range(rows_total + rows_count, 1) = cell.Offset(0, 2).Value
rows_count = rows_count + 1
End If
Next cell

If IsEmpty(Cells(12, 2)) = False Then
record_range(rows_total + rows_count, 2) = Cells(12, 2).Value
record_range(rows_total + rows_count, 4) = Cells(1, 6).Value
record_range(rows_total + rows_count, 6) = Cells(12, 3).Value
record_range(rows_total + rows_count, 5) = Cells(1, 2).Value
record_range(rows_total + rows_count, 5).NumberFormat = "mm/dd/yyyy"
    If Cells(12, 3).Value = "Replace" Then
    record_range(rows_total + rows_count, 3) = 1
    End If
record_range(rows_total + rows_count, 7) = Cells(12, 4).Value
record_range(rows_total + rows_count, 1) = Cells(12, 5).Value
End If

Range(Range("B1"), Range("d12")).ClearContents
Cells(12, 5).ClearContents
Cells(1, 6).ClearContents

End Sub