Date as serial number in vba

[dcdc#1]

Hi

I'm finally getting there...

Can anyone tell me how to convert a date (in standard dd/mm/yy format) to its equivalent Excel serial number? I'm trying to do an application.hlookup and it's only working if I enter the serial number directly rather than using the formatted date.

TIA
Danny

 

[VoG]

Try

=VALUE(A1)

where the date is in A1.

 

[dcdc#1]

sorry - should have made it more clear - this is in vba

 

[VoG]

Try

MsgBox CLng(Range("A1").Value)

 

[dcdc#1]

you're a star - thanks!

 

[Asterix]

this is interesting as I need to do almost the same thing.

Except in VBA I'm trying to find a week commencing date for the current date (i.e. whatever today's "day", the value returned would always be the preceding sunday).

I'm trying to use...

wc_date = CLng(Date - Weekday(Date, vbSunday) - 7)

The only reason I'm using "CLng" is that when I use...

wc_date = Date - Weekday(Date, vbSunday) - 7

...and then try to find wc_date within a range of cells formatted as "dd/mm/yyyy" on the sheet itself, they're not being found (even if I declare wc_date as a variant, so it resolves as a variant/date to match the variant/dates held on the sheet).

Preferably, I'd be able to work out why, without using CLng, it can't find the date on the sheet but if the not finding in this case is a known feature, I'll settle for any help I can get.

Thanks.

 

[Norie]

Asterix

How have you declared wc_date?

If you've declared it as Integer then that could be the problem.

Integers can only store values from 32,768 to 32,767.

So the last date an integer can store is 16th September 1989.

Try declaring as Long.

 

[Asterix]

this is interesting as I need to do almost the same thing.

Except in VBA I'm trying to find a week commencing date for the current date (i.e. whatever today's "day", the value returned would always be the preceding sunday).

I'm trying to use...

wc_date = CLng(Date - Weekday(Date, vbSunday) - 7)

The only reason I'm using "CLng" is that when I use...

wc_date = Date - Weekday(Date, vbSunday) - 7

...and then try to find wc_date within a range of cells formatted as "dd/mm/yyyy" on the sheet itself, they're not being found (even if I declare wc_date as a variant, so it resolves as a variant/date to match the variant/dates held on the sheet).

Preferably, I'd be able to work out why, without using CLng, it can't find the date on the sheet but if the not finding in this case is a known feature, I'll settle for any help I can get.

Thanks.

Thanks, answered my own question (with a little embarrasment on the way - it helps if you don't CLng a dim'd integer)

 

[Asterix]

Asterix

How have you declared wc_date?

If you've declared it as Integer then that could be the problem.

Integers can only store values from 32,768 to 32,767.

So the last date an integer can store is 16th September 1989.

Try declaring as Long.

"On the money" and your post didn't show up for me until 1723hrs GMT (despite refreshing)