String to Date Field 1

[MochaLatte]

I'v tried several formulas and they don’t seem to work.
I am trying to convert a five digit character string “12345” into a date field "mm/dd/yy"

Any help will be greatly appreciated!

MochaLatte

 

[fisheromacse]

what is your 5 digit string consist of?
is it always 5 digits?
i would guess that it is referencing a fixed date and is the number of days since the fixed date. If so, you could do a dateadd to get the date into a 'normal'/human readable format.

 

[MochaLatte]

Yes; I have a string field or column (Table.Fivedigits) which gives me different information depending on the row ID or Account ID, for example:
if the account ID='Alpha' then, the field should return a date;
if the account ID='Beta' then, the filed should reture a time;


I used the formula below for the time row and it works perfectly but I don't how to do the Date row

the whileprintingrecords;
NumberVar Hours;
NumberVar Minutes;
//NumberVar Seconds;
StringVar Timevalue;
If (Table.Fivedigits) = 'Beta' then
(Hours:=Truncate(ToNumber((Table.Fivedigits)3600);
Minutes:=Truncate(Remainder(ToNumber((Table.Fivedigits)3600)/60);
//Seconds:=Remainder(ToNumber({Table.Fivedigits}),60);
Timevalue:=ToText(Hours,'00') + ':' + ToText(Minutes,'00')); //+ ':' + ToText(Seconds,'00'));
Timevalue

 

[HowardHammerman]

can you provide an example of the strings for Alpha and Beta?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[Madawc]

What date is 12345 supposed to be?

There is a DateAdd function, which can add days, hours, minutes etc. to a given date. This may be what you want. Your Crystal should have a HELP funciton to tell you how to use it. It includes DateTime and Time adjustment.

There are also Left and Right and MID functions that will let you chop up a number or numeric code.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[MochaLatte]

Row (Acct ID) -- -- Value (string)
12038721 ---- 62829 (Date)
12038722 ---- 74760 (Time)

I am trying to format the value “62829” (string) to 01/07/13
“62828” should be 01/06/13
“62830” should be 01/08/13
Thanks for your responses!!!

 

[fisheromacse]

So it looks like your date is counting days up since 1841. ???
If so, the formula below should convert the 5 digits into a date you can read/use.

\\{@FindDate}
numbervar tm := {YourTable.YourField};
Date(DateAdd('d',tm,Dateserial(1841,01,01)))


you will need to add the logic to differentiate between the rows that are date vs time.

 

[fisheromacse]

oops, should be:

\\{@FindDate}
numbervar tm := {YourTable.YourField};
Date(DateAdd('d',tm,Dateserial(1841,01,01)-1))

 

[MochaLatte]

Thank you "fishromacse", it works great!