excel assistance

[snapping sam]

I am trying to create two new cols - taking a date in the dd/mm/yy format - and making the mm go to February, March etc. The dd part is a little more complex. I am wanting to change ir to 21st, 22nd, 23rd etc. For the single digit I need !! (two exclamation marks) in the front - i.e. !!1st, !!2nd etc Many thanks in advance! David

[Closer]

first bit is easy and has (at least) two ways of doing it. a) Set the format of the date cell to mmmm b) if you want to display the month in a different cell, use =text(a1,"mmmm") in your new cell - where a1 is the original date As for your second part, all I can say is why do you need !! And not sure how you can get the "st", "nd", "rd", "th" that easily either without getting into code

[Closer]

For the exclamation marks try =IF(TEXT(A1,"d")<"10","!!" & TEXT(A1,"d"),TEXT(A1,"d")) I've given up on the st/nd/rd/th part...microsoft just doesn't have a format for it.....it can be coded, but if possible can you not use it !!!

[Closer]

ok try this as a function (i got bored at work) Function daylit(x) If Format(x, "d") = "12" Then daylit = "12th" ElseIf Format(x, "d") = "11" Then daylit = "11th" ElseIf Right(Format(x, "d"), 1) = "1" Then daylit = Format(x, "d") & "st" ElseIf Right(Format(x, "d"), 1) = "2" Then daylit = Format(x, "d") & "nd" ElseIf Right(Format(x, "d"), 1) = "3" Then daylit = Format(x, "d") & "rd" Else daylit = Format(x, "d") & "th" End If If x < "10" Then daylit = "!!" & daylit End If End Function(/b) It's not pretty, but it works.

[Closer]

whoops..... you also need a ElseIf Format(x, "d") = "13" Then daylit = "13th" Poor QA you see.

[snapping sam]

Hi to "CLOSER" Thanks for taking the time to answer this... I will be printing calendars of images done by children - and their details are supplied at an excel or txt file. I have to manipulate the data ready for printing -hence adding the nd, st etc. My production software has a "Wee Easteregg" built into it for me, which lets me force things to the front by adding the !! (using the ASC11 code it was higher than anything I needed). That way the sort is correct, and not the 1st, 11th, 12th... 19th, 2nd, 20th... etc that would normally happen. Got everything to work great - except I don't follow the longer formula - how do I apply it! (sorry, I don't wish to appear to be a complete numptie - if this were Photoshop I would be able to answer just about any question, but excel...) Hope that explains clearly - and if you could just give me a blow by blow on the great looking formula..... David

[Closer]

Sorry....have only just got back to see that you had replied again !

you need to go into TOOLS > MACRO > VISUAL BASIC EDITOR (depending on what version of excel you are using

Then add the following to the general code

Function daylit(x)

If Format(x, "d") = "12" Then
daylit = "12th"

ElseIf Format(x, "d") = "11" Then
daylit = "11th"

ElseIf Format(x, "d") = "13" Then
daylit = "13th"

ElseIf Right(Format(x, "d"), 1) = "1" Then
daylit = Format(x, "d") & "st"
ElseIf Right(Format(x, "d"), 1) = "2" Then
daylit = Format(x, "d") & "nd"
ElseIf Right(Format(x, "d"), 1) = "3" Then
daylit = Format(x, "d") & "rd"
Else
daylit = Format(x, "d") & "th"
End If

If x < "10" Then
daylit = "!!" & daylit
End If

End Function

you should then be able to select this function to use in any cell in your spreadsheet