Count Dates

[#1]

Dear,

I need to count dates expression if date formate complete entered.

order_date

2011-09-22 15:56:00

2011-09-23 21:01:00

2011-09-23 13:31:00

2011-09-22 19:36:00

2011-09-22 19:56:00

2011-09-21

2011-09-22 19:

2011-09-

Result is 5 which is highlighted in green.

Regards,

Zain

[Gysbert_Wassenaar]

Try count(if(Len(Order_Date)=19,Order_Date)). This is not fool-proof, but may be enough for your purposes.

[#3]

Thanks for reply, you are right any idea if user change order of date, year, month, day and time for example

formate of date is 2011-09-22 15:56:00 but user enter 20-09-2011 15:56:00, so how we catch?

Regards,

Zain.

[nagaiank]

Checking for length is one of the validations.

Validating pattern, validating data (e.g. hh part has number 00 thru 23, mm and ss parts have numbers 00 thru 59, month part has number 01 thru 12, date part has number 01 thru 28, 29, 30, or 31 depending on the month and year, etc.) are also to be performed.

If the data has 04-03-2012, do you interpret this as 3-Apr-2012 or 4-Mar-2012?

The checks that you need to add, depend on the source of data. If the source has bulit in several of these checks, you may skip some of the validations

You can build expressions for validations in the script, depending on what you want to implement.

[Gysbert_Wassenaar · Accepted solution]

This checks if a date can be made from the input with the specified date format:

count(if(isnum(date#(Order_Date,'YYYY-MM-DD hh:mm:ss')) ,Order_Date))

Unfortunately dates as 2011-09-25 19:84:12 are also accepted. So additional test is needed:

isnum(MakeTime(mid(Order_Date,12,2),mid(Order_Date,15,2),Mid(Order_Date,18,2)))

Bringing the total to:

count(if(isnum(date#(Order_Date,'YYYY-MM-DD hh:mm:ss')) and isnum(MakeTime(mid(Order_Date,12,2),mid(Order_Date,15,2),Mid(Order_Date,18,2)))  ,Order_Date))