reformatting dates and finding latest (VBA)

[RobbieC]

Hi there, I have a worksheet with 5 dates in cells (A1, A101, A201, A301 & A401) - they are all formatted dd/mm/yyyy

I need to copy the date from these cells to other cells on another sheet but change the formats to yyyy-mm-dd - I thought I could use:

Date1 = Sheets("Sheet1").Range("A1").Value
Date1 = Format(Date1, "yyyy-mm-dd")
Sheets("Sheet2").Range("A1").Value = Date1

but that didn't work - it just keeps the same format...

Secondly, I need to work out the MAX or latest date in these 5 cells - I thought I could use:

EndDate1 = Sheets("Sheet1").Range("A1").Value
EndDate2 = Sheets("Sheet1").Range("A101").Value
EndDate3 = Sheets("Sheet1").Range("A201").Value
EndDate4 = Sheets("Sheet1").Range("A301").Value
EndDate5 = Sheets("Sheet1").Range("A401").Value
    
EndDateMax = WorksheetFunction.Max(EndDate1, EndDate2, EndDate3, EndDate4, EndDate5)

but it didn't like that either... obviously, doing stuff with dates isn't my strong point

If you can point me in the right direction, I'd be really grateful.

Thanks

 

[JoeMo]

Maybe this will help:

Sub test()
Date1 = Sheets("Sheet1").Range("A1").Value
With Sheets("Sheet2").Range("A1")
    .Value = Date1
    .NumberFormat = "yyyy-mm-dd"
End With
maxDate = Format(Application.Max(Range("A1,A101,A201,A301,A401")), "yyyy-mm-dd")
MsgBox maxDate
End Sub

 

[MrTeeny]

For the date format you just need to set the format of the cell rather than the data as the formatting of the cell will determine how it's displayed not the format of the data

Date1 = Sheets("Sheet1").Range("A1").ValueSheets("Sheet2").Range("A1").Value = Date1
Sheets("Sheet2").Range("A1").NumberFormat = "yyyy-mm-dd"

What problem are you havinng with your MAX function I can't see any reason it shouldn't be working, it should just return the highest number of days

 

[RobbieC]

Thanks guys, I have applied your solutions. The first bit (changing the format) works great!

However, the 'finding the latest date' doesn't appear to work

It is displaying 1899-12-30, even though all 5 cells have a valid 'modern' date in them... why would this be?

 

[JoeMo]

One possibility is that your "dates" are text not numbers. In any empty cell enter: =ISNUMBER(A1) where A1 contains one of your "dates". What does the formula return?

 

[RobbieC]

Thanks Joe, that was it! the cell was formatted as text..... doh!