Custom Date Format needed

[Jaffabfc]

Hi Al,

Been trying to get this custom date format on a file i have but struggling to get it right.

So at the moment going left to right we have dates in the format of 1st Jan 2nd Jan etc
i want the format exactly like that with every date until the end of the year, without Weekends and Bank Holidays but struggling to get over the first hurdle.

Should be able to sort out the weekends/bank holidays with a networkdays() formula.

Can someone point in right direction, i have tried DD MMM but thats not working as it puts a dash in the middle.

THanks

 

[Zot]

Probably this is what you need

Excel Format dates to include st, nd,rd and th - The JayTray Blog

In an eBook “Date Functions”, I showed you a formula that would include st, nd, rd and th to a day number, so 14 November would display as 14th November. I have since been asked by some people if it is possible to format dates to include st, nd, rd and th. The answer is yes, you [...]

blog.thejaytray.com

 

[footoo]

I think the following is included in the link posted by Zot

Step (5) below assumes you have holiday dates listed in A1:A10 of a separate sheet (“Sheet2”).
(1) Custom format A2 : d”th” mmm
(2) Conditional Format A2 with 3 formulas and number formats :
Formula =OR(DAY(A2)=1,DAY(A2)=21,DAY(A2)=31)
Format d”st” mmm
Formula =OR(DAY(A2)=2,DAY(A2)=22)
Format d”nd” mmm
Formula =OR(DAY(A2)=3,DAY(A2)=23)
Format d”rd” mmm
(3) Copy A2 and pastespecial/format to B2
(4) Enter the start date in A2
(5) Enter in B2 : =WORKDAY(A2,1,Sheet2!$A$1:$A$10)
(6) Drag B2 across as far as required)