Dates in Excel VBA (again !) ...

  Heefie 14:21 16 Jan 05

I've got a strange one, I think !!!

Current date is 16 Jan 2005, and in my module I say

Dim CurrDate As Long

CurrDate = Format(Now(), "00000")

This sets CurrDate as 38369

Later in the module I want to compare this date with one from a cell ...

Dim MyNewDate As Long

MyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005

The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...

In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ...

Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

  VoG II 14:25 16 Jan 05

I'm not sure what is goung on but you would be better off working with Dates rather than Longs

Sub test()

Dim mydate As Date

mydate = Now()

MsgBox mydate

End Sub

As VoG™ states switch to a date format.

However the numerical equivalent of 16th January is 38368 and the 17th January is the 38369.


  Simsy 10:19 17 Jan 05

works in conjunction with the "system" time on the PC... i.e. the date/time shown in the system tray.

I believe that this in turn works from info supplied by the BIOS clock.

If these are wildly wrong it's not beyond the bounds of possibility that your system could be on the wrong date...

Worth checking, especially as you got the value for 16 Jan as 38369

(If your BIOS clock is out by 12 hours you could have a date change at midday instead of midnight, and this could mean that you get two different values during the course of the same actual day!)



  Heefie 13:57 17 Jan 05

Vog & Whisperer thanks, I was using LONG because I wanted to add numbers to the date, but it seems to work OK in DATE mode as well, so I've changed that ...

Simsy, according to Date & Time in my System Settings, my date and time now is 13:48:26 on January 17th 2005, using GMT and automatically adjusting for Daylight saving. BUT, if I run the module ...

Dim CurrDate As Date

CurrDate = Format(Now(), "00000")

Gives me 18/01/2005 !!!!!!!!!!!!!!!!!!!!!!!

Where do I look now ?!?!?

  VoG II 17:35 17 Jan 05


CurrDate = Format(Now(), "dd/mm/yyyy")

returns the correct date.

  Heefie 18:18 17 Jan 05

Sorry, I got waylaid by Fantasy Football ... I typed all the following ages ago but forgot to post it !!!

I found that too, the NOW() command was returning the correct date, but the FORMAT command corrupted it !!!

As I was using the FORMAT command simply because I was using CURRDATE as LONG, but I am now using it as DATE, I took the FORMAT command off altogether & used ...

CurrDate = Left$(Now(), 10)

... instead, and it works fine.

No real idea why it was doing what it was doing, but it's sorted now, thanks lads ...


This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…