Recently one of our customer asked how to find cases or emails created on Saturday/Sunday or cases created after normal working hours (i.e. after 18 PM?).
Of course this CRM system had email router which were creating those email and automatic case were created based on this.
I did try to so this using advanced find but it is not possible to get the desired result and try to do this using Excel features and it was really easy, following quick steps for the same using Excel.
Step 1: Export data to excel, so do advanced find on CRM (CRM 4.0 or CRM 2011) to retrieve relevant data and then export that to excel.
Step 2: once exported add new columns, let’s say those columns are called “DAY”, “Time HOUR” and “Time MIN”.
Step 3: put formula to retrieve values for those columns, as shown below. Please note that column G is created on date and based on this column G following formula are created. Please change column name as per your requirement.
COLUMN I (DAY) :
=IF(WEEKDAY(G2)=1,"SUNDAY",IF(WEEKDAY(G2)=2,"MONDAY",IF(WEEKDAY(G2)=3,"TUESDAY",IF(WEEKDAY(G2)=4,"WEDNESDAY",IF(WEEKDAY(G2)=5,"THURSDAY",IF(WEEKDAY(G2)=6,"FRIDAY",IF(WEEKDAY(G2)=7,"SATURDAY",)))))))
COLUMN J (Time Hour):
=HOUR(G2)
COLUMN K (Time Hour):
=MINUTE(G2)
Step 4: after apply this formula to all rows and you will get following result.
Step 5: you can apply standard excel filter to return only cases created on weekends (Saturday/Sunday) or cases created after 18 PM easily.
Hope this helps…
Cheers,
Mayank:)
MayankP's Blog 