Newbie Calculation question

[Jeff Abraham]

I have 2 separate Date fields. I would like a calculation that displays which date is the latest. Thanks, I'm having a brain freeze!

Date Field 1: 9/11/03

Date Field 2: 9/13/03

Calc result: 9/13/03

[BobWeaver]

Max(Date Field 1, Date Field 2)

[Jeff Abraham]

I should have mentioned that the 2 fields are actually Text fields and the values are:

9/11/03 at 09:11:45AM

9/13/03 at 13:33:51PM

Max calc works great for Dates (thanks BobWeaver!) but what about evaluating text fields for the greater value? The Max calc turns out to be 0, of course.

[Lee Smith]

Are you sure that is all now?

Try this one

Max(

TextToDate(LeftWords(Date Field1, 3)),

TextToDate(LeftWords(Date Field2, 3)),

TextToDate(LeftWords(Date Field2,3)))

Lee

[Jeff Abraham]

Thanks, Lee. I should have been more clear in my original post.

Why is the Date Field2 listed twice in the calculation? It is working but the time is getting dropped. Is there a way to also keep the "at 09:11:45AM" in the calc? Or can that be put into a separate field, then concatenated with the Max date field?

Thanks for the help! I'm not that great with calculations yet.

[Ugo DI LUCA]

As a matter of fact, I was wondering, as Lee, if there wasn't "some other hided secrets"...

c_newcalcN = TextToDate(LeftWords(t_datefieldN,3))+TextToNum(Left(Substitute(Substitute(RightWords(t_datefieldN,1),"pm",""),"am",""),2))*3600+TextToNum(Middle(Substitute(Substitute(RightWords(t_datefieldN,1),"pm",""),"am",""), 4,2))*60+TextToNum(Right(Substitute(Substitute(RightWords(t_datefieldN,1),"pm",""),"am",""), 2)).

But are you sure you need PM and AM ?

Change the N to your field Number of course and go with Max(c_newcalcN, c_newcalcN+1,c_newcalcN+2,...)

HTH.

[McCormick]

I have to wonder why these are text fields. Did you inherit data that you have to use? Could you run a script to convert it all, once?

I would have a Date Field, a seperate Time field, and a third Calc Text field to combine the two into the sentence fragments you used above. Then you can manipulate the dates and times more easily.

[Jeff Abraham]

Still not working. I'm just getting a 6-digit number as my final calc. Here are the fields:

art_image = text field contains 9/11/03 at 09:11:45AM

art_text = text field contains 9/13/03 at 13:33:51PM

c_arttext= calculation number field contains:

TextToDate(LeftWords(art_text,3))+TextToNum(Left(Substitute(Substitute(RightWords(art_text,1),"pm",""),"am",""),2))*3600+TextToNum(Middle(Substitute(Substitute(RightWords(art_text,1),"pm",""),"am",""), 4,2))*60+TextToNum(Right(Substitute(Substitute(RightWords(art_text,1),"pm",""),"am",""), 2))

c_artimage = calculation number field contains: TextToDate(LeftWords(art_image,3))+TextToNum(Left(Substitute(Substitute(RightWords(art_image,1),"pm",""),"am",""),2))*3600+TextToNum(Middle(Substitute(Substitute(RightWords(art_image,1),"pm",""),"am",""), 4,2))*60+TextToNum(Right(Substitute(Substitute(RightWords(art_image,1),"pm",""),"am",""), 2))

c_arttatest = calculation number field contains:

Max(c_arttext,c_artimage)

Result = 856992

All of the calculations are set to number results. Is this correct? I'm very confused. Thanks for helping!

[Jeff Abraham]

I'm using Lasso 6 to insert the server date and time into a text field when a user submits a job status online. I probably should have made separate date and time fields, then concatenated them. I have a lot of Lasso code that uses this date/time text field. If I can get the calcs that Ugo sent me working then that would be great. I'm going to be doing a system upgrade in a month or two then I'll be reworking the code. But for now....

[Ugo DI LUCA]

Of course this gives a number result.

The purpose was to convert the day and time to a numeric field

Then you can compare both and take the higher number, using Lee's TextToDate(LeftWords(c_dateN, 3))

c_higherdateAndTime = Case(c_arttext>c_artimage,TextToDate(LeftWords(c_arttext, 3)),TextToDate(LeftWords(c_artimage, 3)))