[VBA] Date as serial appears in one cell, Americanised date in another.

[RockandGrohl]

Hi all, same kind of issue as a few days ago..

NewIndate = Format(DateValue(InDate.Caption), "dd/mm/yyyy")NewOutdate = Format(DateValue(OutDate.Caption), "dd/mm/yyyy")


If NewIndate = "" And NewOutdate = "" Then


MsgBox "Please ensure you have entered a date or date range in the Control Panel", vbOKOnly, "ErRoR!"


Exit Sub
End If


Range("A1").Value = Format(DateValue(NewIndate), "dd/mm/yyyy")
Range("B1").Value = Format(DateValue(NewOutdate), "dd/mm/yyyy")

That's the code, values are entered in the Control panel like "01/09/2019" and "01/12/2019"

They should then be entered into cells A1 and B1 so I can return those values in the caption if I load the control panel again (that's sorted in a different part of code)


The problem is that for some unknown reason, the first date I enter "01/09/2019" (1st September) is stored in A1 as "43474" (9th Jan) and the second date I enter "01/12/2019" is stored in B1 as "12/01/2019"

Very frustrating, any ideas? I had just "Value = NewIndate" originally but it produced the same result.

 

[Norie]

Don't use Format when putting the dates into the cells, use DateValue and format the cell as required.

Range("A1").Value = DateValue(NewIndate)
Range("B1").Value = DateValue(NewOutdate)

 

[RockandGrohl]

Don't use Format when putting the dates into the cells, use DateValue and format the cell as required.

Range("A1").Value = DateValue(NewIndate)
Range("B1").Value = DateValue(NewOutdate)

Thanks Norie, this sorted the problem