Convert Moments (eWFM) to Start Date_Time 1

[cristle]

I'm reporting off an application called eWFM using Crystal Reports XIR2.
There is a field called Start Moment that contains the planned start time of say a lunch break.
However the time is stored as "Moments", which I understand are actually minutes since 30/12/1899 12:00am.

Does anyone know how to convert these "Moments" to an actual Start Date_Time?
Any help would be much appreciated.

Cristle

 

[lbass]

Try:

dateadd("n",{table.moments},datetime(1899,12,30,0,0,0))

-LB

 

[cristle]

Thanks for your reply LB,
However I'm still stuck,

I have a value of 56,807,295 Moments that I believe is actually 02/01/2008 12:15PM.

When I try your formula,
dateadd("n",{DET_SEG.START_MOMENT},datetime 1899,12,30,0,0,0)) I get the result 26/11/1871 05:46:44.

If you've any other thoughts I'd welcome them

Thanks
Cristle

 

[lbass]

If you can provide at least two sets of moments with the corresponding dates these represent, I think we could figure this out.

Recently there was a thread in which one number incorporated two types of time intervals--days and seconds. Is it possible that this is true here?

-LB

 

[cristle]

The "Moments" are only stored as Minutes so I don't think that's the problem.
Here's some sample data.
If I add 1440 minutes to your formula it it works , I wonder if it's failing because we'er traversing a century,

Moments Equivalent_Date_Time
56,807,295 02/01/2008 12:15PM

56,807,535 02/01/2008 16:15PM

Cheers
I appreciate the effort

Cristle

 

[cristle]

Interestingly, if I use the following formula
dateadd("n",(525600*68),#30/12/1899#)
I get 14/12/1967 12:00:00

where 525600 = mins in a year, 68 = 68 years

However when I change the years from 68 to 69 I get
06/11/1832 12:00:00

Cristle

 

[lbass]

Can you provide a couple more samples with different dates in the results? I have something working for 2/1, but am not sure it's a mathematical fluke.

-LB

 

[cristle]

LB,

Here's some more examples

Moments Equivalent_Date_Time
56,804,310 31/12/2007 10:30:00
56,804,565 31/12/2007 14:45:00

Thanks

Cristle

 

[cristle]

HI LB,

Any chance that you could give me the solution you have for 02/01/2008?

Thanks

Cristle

 

[lbass]

Think I cracked it:

stringvar x := totext({table.moment},0,"");
datetime(date(dateadd("d",int(val(x)/1440),date(1899,12,30))),
time(dateadd("n",val(right(x,4))+640,datetime(currentdate,time(0,0,0)))))

-LB

 

[cristle]

LB,

Thanks for you help yesterday.
I now have a solution as follows,

dateadd("n", remainder({DET_SEG.START_MOMENT},1440),dateadd("d",{DET_SEG.START_MOMENT}/1440,datetime (1899,12,30,0,0,0)) )

Cristle