crim13
11-18-2015, 03:28 PM
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
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