Change Date & Time formats (1 Viewer)

[noboffinme]

Hi

I have 2 spreadsheets in one workbook, one spreadsheet has the date & time in 2 columns in the following format;

Worksheet 1

Date Time
31-Dec 11:20 PM
-------------------
Worksheet 2
Date Time
31/12/2011 23:20

As you can see, one spreadsheet has the time in 24 hour clock, I want to concatenate & convert the date & time to then use a macro to see if the date/times match on a record.

I have the code to do the match check but need to convert them to the same format first.

Any suggestions?

 

[Brianwarnock]

31-Dec 11:20 PM

This is not a Date/time format as it has no year.

If it were a valid date field the display format would not matter for a comparison.

Brian

 

[Brianwarnock]

Also be aware that if any of the Date/Times is a timestamp or calculated value it is better to use the Datediff function and test for 0 as the times shown are rounded times, the Datediff does the same rounding but a compare would not so any fractions of a second will give a non equal result.

Brian

 

[Brianwarnock]

I was just about to sign out when the old grey cells spat out some knowledge.
If the cells in Sheet 1 are Text and all of the years are the current year then it is still possible to do the test using datediff.

Call this using =comparedates(cell1ref,cell2ref)

Function comparedates(date1 As String, date2 As Date) As String

If (DateDiff("n", date1, date2) = 0) Then

comparedates = "Equal"
Else
comparedates = "not Equal"

End If

End Function

 

[noboffinme]

Thanks for this Brian,

I didn't post the first date/times completely (as you probably guessed) as I was playing around with Text to Columns to split the date from the time.

Thanks again, it works well !!

Peter