Calculations with different date formats

Hi,

I have a date and time in a single cell in a worksheet, with the format
"dd/mm/yy hh:mm". I have written a function to determine what crew is
working at this particular time. However, VBA has the date format as
mm/dd/yy and this seems to give me the wrong answers. How can I force
VBA to interpret my date format correctly? The function code is as
follows:

Public Function Crew(DTS)
Dim BaseDTS, CrewShifts1, CrewShifts2
Dim ShiftsSince, LookupShift

BaseDTS = #5/2/2005# + 8 / 24 'May 2, 2005 08:00
ShiftsSince = (DTS - BaseDTS) * 2
etc etc
End Function

TIA,
Derek

Hi Derek,

The format does not affect the calculation.
If you return just the #5/2/2005# as result and format it your way, you'll
see that is displayed correctly as 02/05/05 etc

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Derek" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a date and time in a single cell in a worksheet, with the format
> "dd/mm/yy hh:mm". I have written a function to determine what crew is
> working at this particular time. However, VBA has the date format as
> mm/dd/yy and this seems to give me the wrong answers. How can I force
> VBA to interpret my date format correctly? The function code is as
> follows:
>
> Public Function Crew(DTS)
> Dim BaseDTS, CrewShifts1, CrewShifts2
> Dim ShiftsSince, LookupShift
>
> BaseDTS = #5/2/2005# + 8 / 24 'May 2, 2005 08:00
> ShiftsSince = (DTS - BaseDTS) * 2
> etc etc
> End Function
>
> TIA,
> Derek
>

Hi Derek,

> How can I force
> VBA to interpret my date format correctly?

User DateSerial(y,m,d) instead of the #m/d/y# style, which welcomes
confusion.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev