Date and sequential serial numbers

[Peter B]

Hi

I am using a date that i have in an excel sheet in VBA and would like the value to be the sequential serial number and not the date. I am using this value for calculations, and it does not work if the "value" is 12/02/2003 instead 38029

How do I get the value in sequential serial number instead of the date.

Rgds

Peter B

 

[Frank Kabel]

Hi Peter
for calculation the format of this date value should have no affect
(that ist your value '12/02/2003' is a ddate and not a textvalue).
Internally Excel treats both representations as the same (you may have
to format the resulting cell).
Maybe you can post what kind o f calculation you're trying to achieve

Frank

 

[Peter B]

I have dimmed startdato as Integer and would simply add 1. The line that doesnt work is this one:
Startdato = Range("Date").Value + 1

When I am trying to do this, I get the message:
"Run-time error '6':

Overflow"

Any suggestions?

Peter

 

[Bob Phillips]

Peter,

Is the Date cell formatted as date? What is in there? How have you declared
Startdato?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ulrik Gustafsson]

Hi,

Dim startdato as date instead - and it will work like a charm.
I have also included how you can convert the date to serial if you should
need it...

Sub TestTheDate()

Dim startdato As Date
Dim dblStartDatoSerial As Double

startdato = Range("Date").Value + 1

MsgBox startdato

' Convert startdato to serial if you should need that
' you can also use the DATE worksheet function for this.
dblStartDatoSerial = CDbl(startdato)

MsgBox dblStartDatoSerial

End Sub

Hope this helps

/Ulrik

 

[Peter B]

The date cell is formatted as Date yes (in Excel). In that cells is todays date, by using the formula =Today(). I have declared Startdato as an Integer (DIM startdato as Integer)

Rgds

Peter B

 

[Frank Kabel]

Hi Peter
two ideas:
- define startdato as date
- What kind of value is in you range 'Date'

Otherwise this works fine for me

Frank

you may have to

 

[Bob Phillips]

Peter,

I thought so. The problem is that today's date is value 38030, whereas an
integer will only hold 32000+, so it overflows. Declare StartDato as a Long,
and all will be okay. If you could work in time, then declare it as Double.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

The date cell is formatted as Date yes (in Excel). In that cells is todays

date, by using the formula =Today(). I have declared Startdato as an Integer
(DIM startdato as Integer).

 

[Tom Ogilvy]

Bob already advised you as to the problem with having your value as an
integer. Just to add, you can use the Value2 property to get the dateserial
as a number rather than a date (although it should be converted just through
the assignment to a long or double).

--
Regards,
Tom Ogilvy

The date cell is formatted as Date yes (in Excel). In that cells is todays

date, by using the formula =Today(). I have declared Startdato as an Integer
(DIM startdato as Integer).