Convert Actual Date to Integer 2

[shavenlunatic]

Hi,

Hopefully a simple question.

I wish to convert a variable holding the date (format dd/mm/yy) into an integer (and back again at some point)

I expect this is possible, but as my help-files aren't working and I couldn't find an answer to this by searching, I've been forced to ask

Thanks in advance

________
clueless

 

[PHV]

A date is already a number of days, so what is your issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shavenlunatic]

i know its actually processed as dayssince.fractionofday

i just need to know how to return that number from a variable which is "dd/mm/yy"

________
clueless

 

[Bong]

Syntax

DateValue(date)

The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.



_________________
Bob Rashkin

 

[shavenlunatic]

?datevalue("15/07/06")
15/07/2006

I need the integer to return rather than the date string

________
clueless

 

[Loomah]

how about
clng(#16/12/2006#)
or
clng([a1]) where the date is in A1

note use of long type as integer would be out of scope for most dates

unless......

i've just missed the point in which case

day(#16/12/2006#)
or
day([a1])

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[shavenlunatic]

thats it, clng... thanks loomah, *click*

________
clueless

 

[combo]

Current date is too big to be integer, can be represented as long:

Dim dDate As Date, lDate As Long
dDate = Now
lDate = DateValue(dDate)
MsgBox lDate
dDate = lDate + 1
MsgBox "Tomorrow date: " & dDate

combo

 

[shavenlunatic]

haven't had a problem with the number returned being too long to be an integer, i ran a loop running from 1900 to the year 5000 and converted it to integer for each day and it never threw out an error, it returned them all groovy like

________
clueless

 

[SkipVought]

"haven't had a problem with the number returned being too long to be an integer"

Three's a difference between an integer and a variable type Integer. The general term, integer, means a whole number. An Integer type is a 4 byte number that has an upper limit of 32,767 (9/16/1989) That's why if you convert a date to a whole number, you ought to specify a Long variable type and use the CLng function for the conversion instead of CInt.

Skip,

_[red][/red]

 

[Loomah]

I find that a tad strange as the scope of an iteger is -32,768 to 32,767 which means the last date that can be converted to an integer is 16 September 1989!

this is why i used clng rather than cint
and why combo has made the same point

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

I would not recommend Clng to get date part if date contains time, DateValue is natural here. Clng rounds to nearest integer instead of cutting fractional part (Clng(1.75)=2), so you can get next day too!

combo

 

[PHV]

I personally use the Int function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Loomah]

so clng(int([a1]))??
where a1 contains =now() returns 38993
and if it's =today() returns 38993

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​