Get date format dd-mm-yyyy

[RAJESH1960]

Hello JohnnyL
I need your help once again on the date format issue. I am getting the date format as mm/dd/yyyy in the destination sheet due to which the data is copied in the same wrong format to the connected sheets. I need your help once again to change the format to dd-mm-yyyy in the destination sheet which will automatically copy the same format to the other connected sheets.
2A date not able to edit.xlsm

 

[6StringJazzer]

Paging @johnnyL

 

[johnnyL]

Try this in the 'Match_Portal_Tally Subroutine

    With wsDestination
'
        .Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray           ' Display results to DestinationSheet
        DestinationLastRow = .Range("A" & .Rows.Count).End(xlUp).Row                                ' Get last row used in column A of the destination sheeet
'
        .Range("B2:M" & DestinationLastRow).Interior.Color = RGB(146, 208, 80)                      ' Highlight the range green
        .Range("B2:M" & DestinationLastRow).Font.Bold = True                                        ' Make the range Bold
'
        For SheetRow = 2 To DestinationLastRow                                                      ' Loop through rows of the destination sheet
            .Range("F" & SheetRow).Value2 = DateValue(.Range("F" & SheetRow))                       '   Write the Serial Date to the cell
            .Range("M" & SheetRow).Value2 = DateValue(.Range("M" & SheetRow))                       '   Write the Serial Date to the cell
        Next
'
        .Range("F:F").NumberFormat = "dd-mm-yyyy"                                                   ' Format the date the way we want it to appear
        
        .Range("M:M").NumberFormat = "dd-mm-yyyy"                                                   ' Format the date the way we want it to appear
'
        .Range("N2:N" & DestinationLastRow).Formula = "=$C$1 & "" "" & C2" & _
                " & ""  "" & $D$1 & "" "" & D2 & ""  "" & $E$1 & "" "" & E2" & _
                " & ""  "" & $F$1 & "" "" & F2 & ""  "" & $K$1" & _
                " & "" "" & K2 & ""  "" & $M$1 & "" "" & TEXT(M2,""DD-MM-YYYY"")"                   ' Copy Narration Formula to Column N
'
        .Range("N2:N" & DestinationLastRow).Copy                                                    ' Copy formula range into memory
        .Range("N2:N" & DestinationLastRow).PasteSpecial xlPasteValues                              ' Paste just the vales back to range
        Application.CutCopyMode = False                                                             ' Clear clipboard & 'marching ants' around copied range
    End With

I altered 1 line in that.

 

[johnnyL]

Paging @johnnyL

LMAO @6StringJazzer

 

[johnnyL]

@6StringJazzer Why am I always the one 'On-Call'?

 

[RAJESH1960]

Perfect.
I have been trying to edit and correct it since morning. Which line did you exactly edit or add....

 

[johnnyL]

.Range("F" & SheetRow).Value2 = DateValue(.Range("F" & SheetRow)) ' Write the Serial Date to the cell

It was
.Range("F" & SheetRow).Value = .Range("F" & SheetRow).Text ' Write the TextDate to the cell

 

[RAJESH1960]

Thanks JohnnyL.

 

[johnnyL]

Glad to help.