• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1214
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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