VBA returns negative value when calculating difference between date/times

[crim13]

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 30/1/15


However if the data returned from the macro is not recognised as a US date, it doesn't work and returns a false or negative value eg 05/05/15 - anything less than 12/12/15 really.


I have tried so many things to change the lookup data but none of it makes any difference. Formatting the data makes no difference at all. I am fairly certain it is something to do with the way the value's are calculated.
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 second entry in cell K5 for IM1108554. (negative value). It is calculating US format minus UK format and providing a negative value.


A fix may be to change the data to serial or integer for calculations but I am not experienced enough to know how to do this in the code. I did not create this code and I am no expert when it comes to VB.
I am not fussed whether the dates are in US or UK format, as long as the calculation works and returns correctly.


Any help is appreciated.


Evan

[Bob Phillips]

Can you give some details, examples, of what is not calculating correctly, what is the result, what it should be, to make it easier to look at.

[crim13]

Can you give some details, examples, of what is not calculating correctly, what is the result, what it should be, to make it easier to look at.

The best example is in the sheet Record Value, cell K5. The second entry in the cell is 02/06/2015 10:39:41 AM - 03/06/2015 1:11:33 AM = -10648

The correct answer should be 872 which is the amount of minutes between the two date/times.

[SamT]

It would help if you gave us the Tab name of the sheet, so we don't have to guess if the Sheet1 Object is what you are talking anbout and if you told use which "Macro" is giving the problem.

I, for one, don't really feel like reverse enginnering the entire workbook and code to see where the problem is.

[crim13]

It would help if you gave us the Tab name of the sheet, so we don't have to guess if the Sheet1 Object is what you are talking anbout and if you told use which "Macro" is giving the problem.

I, for one, don't really feel like reverse enginnering the entire workbook and code to see where the problem is.

Hi Sam.

Sorry, I am fairly new to forum help.
The macro used is called Execute, which also calls upon a public function called CalcTime.

The macro looks up the information on the tab "Information From SLA table" and copies the filtered and calculated data to the tab Record Value.
I suspect the issue is with the CalcTime function and the way it calculates whether a date occurs on a workday or not (based on the data in the Calendar tab)

The function works for any date that has a value greater than 12/12/15 but for some reason doesnt use a consistent format for any date below that value.

Hope this helps
Evan

[SamT]

Have you tried formatting the Date and datetime Columns as Custom: "yyyy-mmm-dd" and "yyyy-mmm-dd h:mm:ss: AM/PM" Those seems to be universally recognised datetime formats.

I am hoping that fixes it, because that is some hairy code for a hobbyist like me.

[crim13]

Have you tried formatting the Date and datetime Columns as Custom: "yyyy-mmm-dd" and "yyyy-mmm-dd h:mm:ss: AM/PM" Those seems to be universally recognised datetime formats.

I am hoping that fixes it, because that is some hairy code for a hobbyist like me.

Yes, tried all kinds of formatting, nothing changes the outcome.
As I said, it appears to be the way CalcTime or IntHolidays is treating the date values. From what I can tell, it views them as US format when it does the calculation.
I wouldnt mind if it was in US format, but it doesnt treat them all in US format, only the values less than 12/12/15

[crim13]

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)?