Using evaluate and not using evaluate changes date format?

[Gunblade]

Hi there,

Have a question on evaluate in a formula.

If I don't use evaluate and have

Please Login or Register  to view this content.

within my formula (of a much bigger formula) using the British date format of D/M/Y it works fine (The date I am comparing to is of course in the British format).
iniMonth and iniYear being custom fields that the user will enter in the excel sheet to check date ranges.

however if the formula is encapsulated in an evaluate function the value it returns is different as it then takes day and month as month and day thus the 1 is the month and iniMonth is used for the day (I assume it's switching to US date format).

This makes no sense to me.

Does anyone know what's going on and is there a simple way to permanently set date format within the formula?

It can't be set in excel itself as it needs to be portable and other people who use it might use different date formats.

Thanks.

[jindon]

Dates are always US format in vba, so better like

Please Login or Register  to view this content.

[Gunblade]

Ah I see, I guess the non-evaluate method just pastes the formula into excel which is using the british format but when using the evaluate and calculating within vba it auto converts to US.
Thanks better understood.