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

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 wrote:
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

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

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)

"Peter B" wrote in message
...

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

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

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

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

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
Peter B wrote:
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

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)

"Peter B" wrote in message
...
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

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

"Peter B" wrote in message
...
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