Date (Square) Problem

[Anil_Babu_Samineni]

Hello,

Here, if i want to show date like a square b square like that, How to show that?

Like this,,

Let's Assume if date is 1-Aug-2016 Then i want to show like -- for that image,  1 is instead of a and st is instead of 2 -- 1st-Aug-2016("st" should be acting as square like image)

If 2nd Day -- nd

If 3rd Day -- rd

If 4th --- 30th Day -- th

If 31st Day -- st

Can i know how to achieve that? -- =Date(Now(),'DD' & '^rd' & ' ' & '-' & 'MMM-YYYY')

[Anil_Babu_Samineni]

Any Short Expression or else we can go this? Can you confirm the same if there is no possibility other expression we can say.

Thanks for your Effort

Update: I had close this discussion, So if anyone has other approach always welcome to gain more knowledge.

Again thanks Tamil Nagaraj

[tamilarasu]

I am not sure Anil. Someone else might have better solution than mine. Have a good day.

[Anil_Babu_Samineni]

Thanks, But performance is not good while using same scenario. It increase time reload. After that i found one more which is this. I am sure if with SubField not at all good. But time reload taken decreased for 57 Seconds. And I heard Pick Match is more better than If Else Conditions. Do we do same by using Pick Match like Expression2

Expression1:

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '1',Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '2', Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '3', Date(DateField,'DD') & Chr(691)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(7511)&Chr(688)& ', ' & Date(DateField,'MMM YYYY')))) as DateField1

Expression2:

Pick(Match(DateField,"$(=left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '1')",

  "$(=Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '2')",

  "$(=Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '3')"),

  Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

  Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

  Date(DateField,'DD') & Chr(691)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

  Date(DateField,'DD') & Chr(7511)&Chr(688)& ', ' & Date(DateField,'MMM YYYY')) as DateField1;

[tamilarasu]

Anil, Did you try my suggestion?

[Anil_Babu_Samineni]

Tamil, That's working like a charm!! But, It increases the reload time. After that i got some idea related that which i posted on Expression1. So, my intention is to do by using Pick match. Unfortunately, I didn't get it anything. It seems sheet fields / Objects looking Empty on my dashboard.

[tamilarasu · Accepted solution]

Hey Anil,

Sorry for my late reply. You can use below one.

Pick((Match(Right(Day(DateField),1),1,2,3) + Pick(Match(Day(DateField),11,12,13)+1,0,-1,-2,-3))+1,

Date(DateField,'DD' & Chr(7511)&Chr(688) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(738)&Chr(7511) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(8319)&Chr(7496) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(691)&Chr(7496) & ', MMM YYYY'),

) as FormattedDate

I have attached a sample file for your better understanding. If you still have performance issue, then try replacing day function (I am not sure about it but give a try) with SubField function in the above expression.

[Anil_Babu_Samineni]

Sorry for my late reply. You can use below one.

No need to say Sorry Mr. Officer . We are just learners

And thanks some what now i got what i am expecting. And this script decrease few more reload time. Earlier it takes 57 Seconds. But now, 38 Seconds. Thanks again tamilarasu

I may go with this in future level

[tamilarasu]

No problem. Have a nice day Anil.

[alis2063]

Hi Guys,

Can we get  lists of ASCII conversion as in  CHR(xxx) as i am looking  data square as below ,

Data