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" wrote in message
oups.com...
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