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

Max(Date Field 1, Date Field 2)

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.

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

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.

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.

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.

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!

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....

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)))