ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Basic question about dates (https://www.excelbanter.com/excel-programming/451225-basic-question-about-dates.html)

Robert Crandal[_3_]

Basic question about dates
 
Is it possible to determine the integer value of the current date?

For example, I thought this code might help, but it doesn't:

MsgBox CInt(Date) ' Returns overflow error

I get the impression that every date in VBA is represented by
a unique integer. Is that right? If so, how do you find the
integer value for any date?




Auric__

Basic question about dates
 
Robert Crandal wrote:

Is it possible to determine the integer value of the current date?

For example, I thought this code might help, but it doesn't:

MsgBox CInt(Date) ' Returns overflow error

I get the impression that every date in VBA is represented by
a unique integer. Is that right? If so, how do you find the
integer value for any date?


You get an overflow because CInt tries to cram whatever you pass it into a
16-bit integer, which has a max value of 32,767. Use CLng() instead.

--
- There's only one thing to do.
- Hide?
- No!

Robert Crandal[_3_]

Basic question about dates
 
"Auric__" wrote:

You get an overflow because CInt tries to cram whatever you pass it into a
16-bit integer, which has a max value of 32,767. Use CLng() instead.


Great, that's what I was looking for!

Is there a function that does the reverse? For example, if the input is
a long number, the output will be a Date string or Date value?




witek

Basic question about dates
 
Robert Crandal wrote:
Is it possible to determine the integer value of the current date?

For example, I thought this code might help, but it doesn't:

MsgBox CInt(Date) ' Returns overflow error

I get the impression that every date in VBA is represented by
a unique integer. Is that right? If so, how do you find the
integer value for any date?



exactly a unique double because it contains also time


debug.print cdbl(now())

if you want just a date

debug.print clng(now())



it is unique number but not exactly Julian Calendar.
Microsoft for compatibility with Lotus123 made the same mistake.
29-02-1900 does not exist but both count it.


The most tricky part if convert that number back to date. :)




GS[_6_]

Basic question about dates
 
"Auric__" wrote:

You get an overflow because CInt tries to cram whatever you pass it
into a
16-bit integer, which has a max value of 32,767. Use CLng()
instead.


Great, that's what I was looking for!

Is there a function that does the reverse? For example, if the
input is
a long number, the output will be a Date string or Date value?


The ImmediateWindow is your friend!

12/17/2015
?clng(date)
42355

?cdate(42355)
12/17/2015

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Robert Crandal[_3_]

Basic question about dates
 

"Auric__" wrote:

You get an overflow because CInt tries to cram whatever you pass it into a
16-bit integer, which has a max value of 32,767. Use CLng() instead.


I assume that a long number is 32 bits? That gives up to 4,294,967,295
numbers. I'm just curious, but what is the maximum date that can
be represented by Excel? Is it 12/31/9999?




Claus Busch

Basic question about dates
 
Hi Robert,

Am Tue, 22 Dec 2015 01:45:22 -0700 schrieb Robert Crandal:

I assume that a long number is 32 bits? That gives up to 4,294,967,295
numbers. I'm just curious, but what is the maximum date that can
be represented by Excel? Is it 12/31/9999?


integer numbers go from -32,768 to 32,767
long numbers go -2,147,483,648 bis 2,147,483,647

The greatest date is
12/31/9999 23:59 = 2958465.99930556


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Robert Crandal[_3_]

Basic question about dates
 
"Claus Busch" wrote:

integer numbers go from -32,768 to 32,767
long numbers go -2,147,483,648 bis 2,147,483,647

The greatest date is
12/31/9999 23:59 = 2958465.99930556


That's what I thought. Wow! Microsoft should
have it fixed by the year 10,000 so that Office
supports bigger dates, haha.




GS[_6_]

Basic question about dates
 
"Claus Busch" wrote:

integer numbers go from -32,768 to 32,767
long numbers go -2,147,483,648 bis 2,147,483,647

The greatest date is
12/31/9999 23:59 = 2958465.99930556


That's what I thought. Wow! Microsoft should
have it fixed by the year 10,000 so that Office
supports bigger dates, haha.


You mean.., "assuming we're still here!"<haha

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com