How to use datevalue function

[crystal28]

I have a formula that returns a number in the format 12/12/2008,it still is not a date formula,its a number,i would like to convert that value into date,i tried using the datevalue function but it says BadDateFormat string,how to use this function...

 

[jdemmi]

You're going to have to do something like this...there are surely other ways...this is one

date(val(replace('12/12/2008', "/","")[5 to 8]),val(replace('12/12/2008', "/","")[3 to 4]),val(replace('12/12/2008', "/","")[1 to 2]))


Make sense?

The replace statement removes the /'s giving us a string of numbers. This allows the val statement to be used.

As I said there are probably other ways...maybe some even easier...but this will work.

NOTE -- you could always perform the replace in a different formula and then use that formula here. Might be a little cleaner to the eye

-- Jason
"It's Just Ones and Zeros

 

[Madawc]

I'd have thought you could do it with

Cdate({your.value})

The DateValue command is normally used to set a specific dated, e.g.

 DateValue(2008, 12, 12)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I have trouble believing that 12/12/2008 is really a number datatype, since I think it would calculate, instead of maintaining this display, i.e., 12 divided by 12 divided by 2008. Can you verify the datatype by observing the tooltext when you run your mouse over the field?

-LB

 

[jdemmi]

Madawc -- I tested that and it worked for me. Good, catch. I assumed/thought that CDate expected the string in a specific format (other than mm/dd/yyyy).

Like I said....many ways to skin a cat. Yours, however, is much cleaner.

-- Jason
"It's Just Ones and Zeros

 

[crystal28]

12/12/2008 is not a number,its a value returned from a function in which i concatanate a string from extracting 2 characters,4 characters from another string to form a date like value.

Also is there a function to calculate the datedifference from todays date minus a specific date,i think i tried date-whatever date that is and get a numeric value,is this correct?

 

[jdemmi]

DateDiff

From the help file

DateDiff (intervalType, startDateTime, endDateTime)

DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)

intervalType is a String expression that is the interval of time you use to calculate the difference between startDateTime and endDateTime. Possible values can be:

yyyy Year

q Quarter

m Month

y Day of year

d Day (both "y" and "d" find the difference in days)

w Number of weeks between startDateTime and endDateTime

ww Number of firstDayOfWeek's between startDateTime and endDateTime

h Hour

n Minute

s Second

startDateTime is the first DateTime value used in calculating the difference.

endDateTime is the second DateTime value used in calculating the difference.

firstDayOfWeek is an optional constant specifying the first day of the week. If not specified, crSunday is assumed. See Day of Week constants.

Returns -- A Number value.

Action -- DateDiff returns a number of time intervals between two specified dates.



-- Jason
"It's Just Ones and Zeros

 

[lbass]

Please tell us whether your field is formatted: MM/dd/yyyy or dd/MM/yyyy.

-LB