Convert Date and Time to number

[kadara]

Hi,

How could I convert the date and time (for example '15.02.2013 19:58:50' ) in 2 (or more) decimal number?

 

[SkipVought]

DateSerial() and TimeSerial() Functions

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

maybe the DateValue() & TimeValue() functions would be more direct


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

I'm not sure any of those produce the result the OP is looking for ... CDbl seems a better candidate - but is all dependant on what the OP really means. Are they looking for the underlying numeric representation of a specific day/date? Or something else?

 

[SkipVought]

actually the DOTS need to be replaces by SLASH or DASH... and D/m/yyyy will not work consistently using DateValue or any other DIRECT conversion, I do not believe. Better to parse into DateSerial(year,month,day)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kadara]

I would like to do a similar thing like in Excel. In Excel you have a possibility to change the date-time format (like '15.02.2013 19:58:50') in number format (like 41320,82).

 

[SkipVought]

How would you get your data into Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kadara]

No, I dont't have any data in Excel, I just gave an example.
I would like to do the similar thing the Excel does.

 

[SkipVought]

if you IMPORT your file via Data > Get External Data > Text files, rather than OPEN the file in Excel, then you can control the column CONVERSION, by parsing on SPACE to separate the date text from the time text and then specifying the date text conversion type as DMY.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

date-time format (like '15.02.2013 19:58:50') in number format (like 41320,82).

1) loose the DOTS!!! that is not a recognizable date character.

2) parse your date into YYYY MM DD and load into DateSerial

3) sum the DateSerial and TimeValue to get the value you want.

MyDateValue = DateSerial(yyyy,mm,dd) + TimeValue("19:58:50")

This is a NUMBER

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kadara]

OK, thanks.

I just make an other function:

MsgBox DataToNumber("15.02.2013", "19:58:50")

Function DataToNumber(tmpDate, tmpTime)

	DataToNumber = CDbl(CDate(tmpDate)) + CDbl(CDate(tmpTime))

End Function

 

[SkipVought]

i get an type missmatch error on

CDate(tmpDate)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kadara]

probably you have another date format setting on your OS. Try to change the 15.02.2013 to 02/15/2013

 

[guitarzan]

Skip: that all depends on your regional settings.

 

[strongm]

>like 41320,82

Yep. CDbl is your friend here.

>loose the DOTS!!! that is not a recognizable date character.

Depends on your regional settings (and the fact that the OP uses , as a decimal seperator indicates that they are in a different region, possibly Germany which uses . for date seperators and , for the decimal seperator)

>CDbl(CDate(tmpDate)) + CDbl(CDate(tmpTime))

Actually

CDbl(CDate("15.02.2013 19:58:50"))

should do the trick.

 

[SkipVought]

it is the DOTS in my case. change to DASHs and I get 41320.83

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Just for grins, thy this
[tt]
MsgBox DataToNumber("12.02.2013", "19:58:50") & DataToNumber("13.02.2013", "19:58:50")
[/tt]
and tell me the INTEGER values that get displayed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DTracy]

What I did one time was wrote a Gregorian to Julian function and a Julian to Gregorian function to switch the dates back and forth. Add the current time as a decimal value to the end of the Julian date and store. When recovering, take the decimal value off the Julian before conversion and change the decimal back to hours minutes seconds as needed. The whole thing packs the date/time down to where you can save a couple of bytes in storage. Years ago that was very important. As far as doing stuff with the dates, I think it's easier if they are strings - that's just me.

Dave.