Formatting Dates

[Ron Cates]

Hi All,

I would like to know the best way to deal with the format of a date field. Is there a way to reformat the date on entry to include leading zeros so that they can sort propperly?

Thanks for any help :

[Raybaudi]

A date field contains a value that can ALWAYS sort properly.

To see leading zeros you can format that field by changing the date format.

[Ron Cates]

Hmmm. I found the date format but it didn't help. I am using a portal sorting technique that does not sort the date right. But I do remember being warned that that would be the case. Originally I didn't expect to have any dates in this portal, but things changed. Good lesson there about being prepared for the unforseen. Guess I'll have to dig in to this solution and see what can be done.

Thanks

[Lee Smith]

Verify that the Date field is "[color:red]Date" and not [color:blue]text.

[Ron Cates]

It is. The problem is that I am using a calculation based portal sort solution. It's the one that drops the field name in to a sort field. I know there was a solution for the date but at the time I implimented the solution I barely understood the technique and the date solution added more complication than I could handle. And I didn't think I would need it (Smacking forhead).

[TheTominator]

For a date field called theDate,

Year(theDate) & Right("0" & Month(theDate); 2) & Right("0" & Day(theDate); 2)

will generate

"20100412" when given April 12, 2010

This string is suitable for adding to another string for a compound key that needs to be sortable.

[Ron Cates]

Hmmm... Not exactly sure how to use that information in my solution but somehow it seems there should be a way? Should I do something like create duplicates of the two date fields I need. Sort by one that is formatted as described above and show the other? Just shootin' in the dark here...

[Raybaudi]

I am using a portal sorting technique that does not sort the date right.

Post an example file that uses that technique.

[Ron Cates]

Here is my sample of the sort technique I am using.Just click the column headers to sort.

Sort_Sample.zip

[TheTominator]

I changed the two unstored calculations used for the sort keys to properly handle Date fields.

The portal sort method you have made use of GetField(). My modified technique adds Evaluate() and FieldType() to detect and handle the field of type date.

Sort_Sample_revised_fp7.zip

[Ron Cates]

You are amazing! Thank you so much for all your help :

[Raybaudi]

Another one...( with a simple GetAsNumber )

Sort_Sample2.zip

[TheTominator]

Yes, GetAsNumber() is probably the better way to go.

[Ron Cates]

The first worked great for me and I love that I didn't have to code for the field names. I pasted the formula into my sort fields and it worked for both the date fields in my layout.