Calculations with different date formats

D

Derek

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
 
N

Niek Otten

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
 
S

Stephen Bullen

Hi Derek,
How can I force
VBA to interpret my date format correctly?
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding Date in an overseas format 20
Need advice re: date without slashes 6
Different Date formats in text to be recognised as date value 8
Date not formatting properly 3
Date format Workbooks Open 1
Today's Date 1
Howw can I make a function return a date in date format 12
Delete rows based on date input 3

Top