• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

convert number to date

i am tapping into an excel sheet as my database source.  I have a settlement date column that is in MM/DD/YY format but when its pulled down into crystal reports it shows the date serial instead of the date format...and crystal thinks its a number so its not letting me change formatting to date.  So I figure I could just use a formula field to convert the number to date format...i know you can do this with string, but how about numbers?

for string i know you can do the following:
date(tonumber(left({table.datestring},4)),tonumber(mid({table.datestring},5,2)),tonumber(right({table.datestring},2)))

how can I work this for numbers to date?
0
IO_Dork
Asked:
IO_Dork
1 Solution
 
FlysterCommented:
Have you tried:

 Format(tonumber,"mm/dd/yy")

Flyster
0
 
IO_DorkAuthor Commented:
no i have not, how is that incorporated/used?
0
 
mlmccCommented:
WHat is it showing in Crystal?
Can you give an example?

mlmcc
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
IO_DorkAuthor Commented:
in excel the date is 7/9/97, in cyrstal that same date is displayed as 35,620.00
0
 
richlandoCommented:
stringvar stryear;
stringvar strmonth;
stringvar strday;

strday:= {table.datestring}[5 to 6];
strmonth:= {table.datestring}[3 to 4];
stryear:= {table.datestring}[1 to 2];

if yyear < "50" then
date(tonumber(stryear)+2000,tonumber(strmonth),tonumber(stryear))
else
date(tonumber(stryear)+1900,tonumber(strmonth),tonumber(stryear))
0
 
mlmccCommented:
That looks like the number of days since 31 Dec 1899

Try this formula

DateSerial(1899,12,{YourField}+30)

Another one is

DateAdd('d',{YourField},Date(1899,12,31))

mlmcc
0
 
mlmccCommented:
richlando - The field is a number not a string.

mlmcc
0
 
IO_DorkAuthor Commented:
that worked perfectly (the first dateserial formula you suggested).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now