Need date-time formula 1

[celauritz]

I have a table with five different columns that would figure date and time. They are all numerical...

Century (20)
Year (8)
Month (6)
Day (3)
Time (1350)

I need to write a formula to get them all into one date/time field. What is in the parenthesis is just an example of what one field in each column would say for anything.

For instance, Century, at this time is 19 or 20
Year is 0 to 99
Month is 1 to 12
Day is 1 to 31
and Time is 0000 to 2359 not counting anything that is like ##60 or higher.

I did get a date formula off this forum about a year ago that is as follows:


NumberVar input := (1000000*{Century})+(10000*{Year})+(100*{Month})+({Day);
If input < 19720101 then Date (1972, 01, 01) else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )


The reason for the imput < part is because anyone that started service with us before 1/1/1972 has all zeroes in each field, and we don't have any data to exactly when they started, so we just use that date for the handful of people who have been with us that long. Is there any way to tie the time into that formula, or do I need a new one?

Thanks in advance...

 

[lbass]

Please explain in what way your current formula is not working.

-LB

 

[celauritz]

My current formula works, but it doesn't include the time at all. I need the time included in the formula and to make it a date/time field.

 

[lbass]

Okay, I see. Try:

NumberVar input := (1000000*{Century})+(10000*{Year})+(100*{Month})+({Day);
datevar x;
timevar y;
If input < 19720101 then
x := Date (1972, 01, 01) else
x := Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) );
If input < 19720101 then
y := time(0,0,0) else
y := time(val(left(totext({time},"0000"),2), val(right(totext({time},"0000"),2),0);
datetime(x,y)

-LB

 

[celauritz]

Thanks, it was missing a close parenthesis or two, but it worked perfectly...