VBA help calculating UK and US date/times

Hi guru's/geniuses/masterminds and helpers

I have this quite good macro that looks at the data in 1 sheet and neatly collates the information with some calculation results in a 2nd sheet.

The issue that I have is sheet 1 contains many dates in US format and when they are calculated doesn't return the correct result.

I have narrowed it down to this. If the data returned from the macro is recognised as a US date, it works fine eg 15/1/15

However if the data returned from the macro is not recognised as a US date, it doesnt work and returns a false or negative value eg 05/05/15

I have tried so many things to change the lookup data but none of it makes any difference. I think it is something to do with the way the value's are calculated.

Any help is appreciated.

Evan

Hi

No need for VBA

If your date is in cell b2 (as it is in your sheet) set another column =0+b2
The zero will tell excel that you want a numeric value for the date and it will figure out the USA format
All you then have to do is format the new cell as UK format (Home>Format>Format Cells>Custom and then "dd mmm yy" in the custom field which will display in UK format

I have done so in column G of your spreadsheet as attached

Thanks for trying Graham, I checked your example but it doesn't appear to have fixed the issue. I still receive negative values where they should be positive integers.

I should point out that I did not write this code and I am no expert when it comes to VB.

Steps taken - Remove blank column E. Changed column F to format "dd/mm/yy". Change Calendar A:A to same format.
Update VB script for dtDate = oSheet.Cells(lngRowCount, 6)

Output on Record Value tab still treats it as US format. I think it does this automatically when calculating regardless of the source formatting. refer to the first entry in K5 for IM1108554.
Any value that spans greater than 1 day and contains a date that could be perceived to be in US format is affected. eg anything less than 12/12/15

I appreciate you looking at this Graham, hopefully Keebellah is able to work out something with the DateSerial and TimeSerial codes.
I need to be able to measure the time difference to the minute between the two dates so hopefully this works.

This issue has now been resolved by StephenCrump at MrExcel forum.

The fixed comment is here:

Ahh! Now I see your problem.

Does it work if you change this line:

If strPr > 2 Then intHolidays = Application.WorksheetFunction.CountIfs(Range("Dates"), ">=" & DateValue(dtSt), Range("dates"), "<=" & DateValue(dtEn), Range("working"), "N")

to

If strPr > 2 Then intHolidays = Application.WorksheetFunction.CountIfs(Range("Dates"), ">=" & CLng(dtSt), Range("dates"), "<=" & CLng(dtEn), Range("working"), "N")

As a separate question, I haven't tried to follow what all your code is doing, so I'm not sure whether when you're doing this comparison you need to be working with integer dates (i.e. not including the time fraction)?