|
List Info
Thread: Strange time format...
|
|
| Strange time format... |
|
2007-01-26 02:18:53 |
|
| Hi!
Once again in some kind of troubles when trying to communicate with a
spreadsheet using a macro.
I created a dialog with a time field in it. The problem is that the time
format is just a number, for example if it's 18:45, the time field will
output 18450000 and the type seems to be double, which I find strange
because it seem to only output (long) integers anyway. The same thing goes
for the date field. 2007-01-26 (today) is output as 20070126 from the date
field.
Since I want time and date to be copied to spreadsheet cells using its date
and time formats, I need to convert those "integers" to something that shows
up as date and time in the spreadsheet. I did that this, probably
unnecessary way:
The cells are pre-formatted as date and time respectively, so the macro does
nothing to the cell formats. I created these two functions for converting
Control field time and date to the numberformat that can be formatted to
readable date and time:
'***************************************************************************
Function DateCtlFieldToDate(Dt As Long) As Date
Dim Y As Long, M As Long, D As Long
Y=Int(Dt/10000)
M=Int(Dt/100)-Y*100
D=Dt-Y*10000-M*100
DateCtlFieldToDate=DateSerial(Y,M,D)
End Function
'***************************************************************************
Function TimeCtlFieldToTime(Tm As Long) As Date
Dim H As Long, M As Long, S As Long
H=Int(Tm/1000000)
M=Int(Tm/10000)-H*100
S=Int(Tm/100)-H*10000-M*100
TimeCtlFieldToTime=TimeSerial(H,M,S)
End Function
'***************************************************************************
Now to my question:
Do I really need these two functions? Isn't there an easier way? I couldn't
find a built in function for doing this, so that's why I tried to create my
own maybe not so fast ones.
Besst regards
Johnny
|
| Re: Strange time format... |
|
2007-01-26 02:30:54 |
|
| Forget about what I said about the type of Time and Date from those fields
in the dialog I was talking about. I checked again and they are Long, not
Double. Sorry for missleading... =)
But I still wonder about the rest of what I wrote...
Johnny
2007/1/26, Johnny Andersson gmail.com>:
>
> Hi!
>
> Once again in some kind of troubles when trying to communicate with a
> spreadsheet using a macro.
> I created a dialog with a time field in it. The problem is that the time
> format is just a number, for example if it's 18:45, the time field will
> output 18450000 and the type seems to be double, which I find strange
> because it seem to only output (long) integers anyway. The same thing goes
> for the date field. 2007-01-26 (today) is output as 20070126 from the date
> field.
>
> Since I want time and date to be copied to spreadsheet cells using its
> date and time formats, I need to convert those "integers" to something that
> shows up as date and time in the spreadsheet. I did that this, probably
> unnecessary way:
>
> The cells are pre-formatted as date and time respectively, so the macro
> does nothing to the cell formats. I created these two functions for
> converting Control field time and date to the numberformat that can be
> formatted to readable date and time:
>
>
> '***************************************************************************
> Function DateCtlFieldToDate(Dt As Long) As Date
> Dim Y As Long, M As Long, D As Long
>
> Y=Int(Dt/10000)
> M=Int(Dt/100)-Y*100
> D=Dt-Y*10000-M*100
> DateCtlFieldToDate=DateSerial(Y,M,D)
> End Function
>
>
> '***************************************************************************
> Function TimeCtlFieldToTime(Tm As Long) As Date
> Dim H As Long, M As Long, S As Long
>
> H=Int(Tm/1000000)
> M=Int(Tm/10000)-H*100
> S=Int(Tm/100)-H*10000-M*100
> TimeCtlFieldToTime=TimeSerial(H,M,S)
> End Function
>
> '***************************************************************************
>
>
> Now to my question:
> Do I really need these two functions? Isn't there an easier way? I
> couldn't find a built in function for doing this, so that's why I tried to
> create my own maybe not so fast ones.
>
> Besst regards
>
> Johnny
>
>
|
| Re: Re: Strange time format... |
|
2007-01-26 04:39:51 |
|
Message de Johnny Andersson date 2007-01-26 09:30 :
> Forget about what I said about the type of Time and Date from those fields
> in the dialog I was talking about. I checked again and they are Long, not
> Double. Sorry for missleading... =)
>
> But I still wonder about the rest of what I wrote...
>
> Johnny
>
> 2007/1/26, Johnny Andersson gmail.com>:
>>
>> Hi!
>>
>> Once again in some kind of troubles when trying to communicate with a
>> spreadsheet using a macro.
>> I created a dialog with a time field in it. The problem is that the time
>> format is just a number, for example if it's 18:45, the time field will
>> output 18450000 (...). The same thing
>> goes
>> for the date field. 2007-01-26 (today) is output as 20070126 from the
>> date
>> field.
>>
>> (...) I created these two functions for
>> converting Control field time and date to the numberformat that can be
>> formatted to readable date and time:
>>
>>
>> '***************************************************************************
>>
>> Function DateCtlFieldToDate(Dt As Long) As Date
>> Dim Y As Long, M As Long, D As Long
>>
>> Y=Int(Dt/10000)
>> M=Int(Dt/100)-Y*100
>> D=Dt-Y*10000-M*100
>> DateCtlFieldToDate=DateSerial(Y,M,D)
>> End Function
>>
>>
>> '***************************************************************************
>>
>> Function TimeCtlFieldToTime(Tm As Long) As Date
>> Dim H As Long, M As Long, S As Long
>>
>> H=Int(Tm/1000000)
>> M=Int(Tm/10000)-H*100
>> S=Int(Tm/100)-H*10000-M*100
>> TimeCtlFieldToTime=TimeSerial(H,M,S)
>> End Function
>>
>> '***************************************************************************
>>
>>
>>
>> Now to my question:
>> Do I really need these two functions? Isn't there an easier way? I
>> couldn't find a built in function for doing this, so that's why I
>> tried to
>> create my own maybe not so fast ones.
AFAIK there is no existing conversion function for time.
For date, you get from the TimeField control a value which is an ISO
date format. Here Basic provides a function:
Dim Tm As Long, Tm2 As Date
Tm2 = CDateFromISO(Tm)
Bernard
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe api.openoffice.org
For additional commands, e-mail: dev-helpapi.openoffice.org
|
| Re: Re: Strange time format... |
|
2007-01-27 01:07:48 |
Thanks! I found it in help now that you mentioned it and I
remember that I
actually seen it before. Thanks!
Maybe I could suggest the functions CTimeToISO and
CTimeFromISO to be
included in OpenOffice.org BASIC in a future version, what
do you think?
Thanks again!
Johnny
2007/1/26, Bernard Marcelly <marcellyclub-internet.fr>:
>
> Message de Johnny Andersson date 2007-01-26 09:30 :
> > Forget about what I said about the type of Time
and Date from those
> fields
> > in the dialog I was talking about. I checked again
and they are Long,
> not
> > Double. Sorry for missleading... =)
> >
> > But I still wonder about the rest of what I
wrote...
> >
> > Johnny
> >
> > 2007/1/26, Johnny Andersson <gurus.knugumgmail.com>:
> >>
> >> Hi!
> >>
> >> Once again in some kind of troubles when
trying to communicate with a
> >> spreadsheet using a macro.
> >> I created a dialog with a time field in it.
The problem is that the
> time
> >> format is just a number, for example if it's
18:45, the time field will
> >> output 18450000 (...). The same thing
> >> goes
> >> for the date field. 2007-01-26 (today) is
output as 20070126 from the
> >> date
> >> field.
> >>
> >> (...) I created these two functions for
> >> converting Control field time and date to the
numberformat that can be
> >> formatted to readable date and time:
> >>
> >>
> >>
>
'***********************************************************
****************
> >>
> >> Function DateCtlFieldToDate(Dt As Long) As
Date
> >> Dim Y As Long, M As Long, D As Long
> >>
> >> Y=Int(Dt/10000)
> >> M=Int(Dt/100)-Y*100
> >> D=Dt-Y*10000-M*100
> >> DateCtlFieldToDate=DateSerial(Y,M,D)
> >> End Function
> >>
> >>
> >>
>
'***********************************************************
****************
> >>
> >> Function TimeCtlFieldToTime(Tm As Long) As
Date
> >> Dim H As Long, M As Long, S As Long
> >>
> >> H=Int(Tm/1000000)
> >> M=Int(Tm/10000)-H*100
> >> S=Int(Tm/100)-H*10000-M*100
> >> TimeCtlFieldToTime=TimeSerial(H,M,S)
> >> End Function
> >>
> >>
>
'***********************************************************
****************
> >>
> >>
> >>
> >> Now to my question:
> >> Do I really need these two functions? Isn't
there an easier way? I
> >> couldn't find a built in function for doing
this, so that's why I
> >> tried to
> >> create my own maybe not so fast ones.
>
> AFAIK there is no existing conversion function for
time.
> For date, you get from the TimeField control a value
which is an ISO
> date format. Here Basic provides a function:
>
> Dim Tm As Long, Tm2 As Date
> Tm2 = CDateFromISO(Tm)
>
> Bernard
>
>
------------------------------------------------------------
---------
> To unsubscribe, e-mail: dev-unsubscribeapi.openoffice.org
> For additional commands, e-mail: dev-helpapi.openoffice.org
>
>
|
|
| Re: Re: Strange time format... |
|
2007-01-27 02:56:21 |
I also noticed that the help section about it suggested that
the argument of
CDateFromISO should be String. I experimented with it a bit
and I found that
Long works fine. However I forgot to try String.
Or in other words:
Help suggests CDateFromISO("20070127")
I tried CDateFromISO(20070127) and it worked fine. However I
forgot to try
what help suggested, so maybe both work.
Johnny
2007/1/27, Johnny Andersson <gurus.knugumgmail.com>:
>
> Thanks! I found it in help now that you mentioned it
and I remember that I
> actually seen it before. Thanks!
>
> Maybe I could suggest the functions CTimeToISO and
CTimeFromISO to be
> included in OpenOffice.org BASIC in a future version,
what do you think?
>
> Thanks again!
>
>
> Johnny
>
> 2007/1/26, Bernard Marcelly <marcellyclub-internet.fr>:
> >
> > Message de Johnny Andersson date 2007-01-26 09:30
:
> > > Forget about what I said about the type of
Time and Date from those
> > fields
> > > in the dialog I was talking about. I checked
again and they are Long,
> > not
> > > Double. Sorry for missleading... =)
> > >
> > > But I still wonder about the rest of what I
wrote...
> > >
> > > Johnny
> > >
> > > 2007/1/26, Johnny Andersson <
gurus.knugumgmail.com>:
> > >>
> > >> Hi!
> > >>
> > >> Once again in some kind of troubles when
trying to communicate with a
> > >> spreadsheet using a macro.
> > >> I created a dialog with a time field in
it. The problem is that the
> > time
> > >> format is just a number, for example if
it's 18:45, the time field
> > will
> > >> output 18450000 (...). The same thing
> > >> goes
> > >> for the date field. 2007-01-26 (today) is
output as 20070126 from the
> >
> > >> date
> > >> field.
> > >>
> > >> (...) I created these two functions for
> > >> converting Control field time and date to
the numberformat that can
> > be
> > >> formatted to readable date and time:
> > >>
> > >>
> > >>
> >
'***********************************************************
****************
> > >>
> > >> Function DateCtlFieldToDate(Dt As Long)
As Date
> > >> Dim Y As Long, M As Long, D As Long
> > >>
> > >> Y=Int(Dt/10000)
> > >> M=Int(Dt/100)-Y*100
> > >> D=Dt-Y*10000-M*100
> > >> DateCtlFieldToDate=DateSerial(Y,M,D)
> > >> End Function
> > >>
> > >>
> > >>
> >
'***********************************************************
****************
> > >>
> > >> Function TimeCtlFieldToTime(Tm As Long)
As Date
> > >> Dim H As Long, M As Long, S As Long
> > >>
> > >> H=Int(Tm/1000000)
> > >> M=Int(Tm/10000)-H*100
> > >> S=Int(Tm/100)-H*10000-M*100
> > >> TimeCtlFieldToTime=TimeSerial(H,M,S)
> > >> End Function
> > >>
> > >>
> >
'***********************************************************
****************
> >
> > >>
> > >>
> > >>
> > >> Now to my question:
> > >> Do I really need these two functions?
Isn't there an easier way? I
> > >> couldn't find a built in function for
doing this, so that's why I
> > >> tried to
> > >> create my own maybe not so fast ones.
> >
> > AFAIK there is no existing conversion function for
time.
> > For date, you get from the TimeField control a
value which is an ISO
> > date format. Here Basic provides a function:
> >
> > Dim Tm As Long, Tm2 As Date
> > Tm2 = CDateFromISO(Tm)
> >
> > Bernard
> >
> >
------------------------------------------------------------
---------
> > To unsubscribe, e-mail: dev-unsubscribeapi.openoffice.org
> > For additional commands, e-mail: dev-helpapi.openoffice.org
> >
> >
>
|
|
[1-5]
|
|