|
Custom fill series?
I need to have a collumn in my spreadsheet which will display every Sunday's
date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
Here is a formula for an adjacent column. You could use a macro to ask for a
date and fill in =DAY(D7)&LOOKUP(DAY(D7),{1,2,3,4},{"st","nd","rd", "th"})&TEXT(D7," mmm") -- Don Guillett SalesAid Software "martinkorner" wrote in message ... I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
Wow - that is just what I wanted!
Thank you so much. Martin "Don Guillett" wrote: Here is a formula for an adjacent column. You could use a macro to ask for a date and fill in =DAY(D7)&LOOKUP(DAY(D7),{1,2,3,4},{"st","nd","rd", "th"})&TEXT(D7," mmm") -- Don Guillett SalesAid Software "martinkorner" wrote in message ... I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
You can enter a Sunday date, and the right click and drag down as needed.
When the mouse is released, the bottom choice in the option window is 'Series'. Click on that, and enter 7 in the 'StepValue' box, then <OK. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "martinkorner" wrote in message ... Wow - that is just what I wanted! Thank you so much. Martin "Don Guillett" wrote: Here is a formula for an adjacent column. You could use a macro to ask for a date and fill in =DAY(D7)&LOOKUP(DAY(D7),{1,2,3,4},{"st","nd","rd", "th"})&TEXT(D7," mmm") -- Don Guillett SalesAid Software "martinkorner" wrote in message ... I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
Nel post
*martinkorner* ha scritto: I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin Hi Martin, I suppose you have the real dates in column A, starting from A1 (in A2 type =A1+7 and copy down). In B1 copy this formula: =DAY(A1)&IF(DAY(A1)=1,"st",IF(DAY(A1)=2,"nd",IF(DA Y(A1)=3,"rd","th")))&" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy") and fill down. IMHO there is no simple way to have st, nd, rd and th as superscript, maybe with a macro, but I can't support you that way. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Custom fill series?
If you liked the formula you will really like this. Position the cursor
where you want the dates to start. Execute the macro. You will be asked for the first date and the number of weeks desired. Sub datefillcustom() ac = ActiveCell.Column ar = ActiveCell.Row - 1 md = InputBox("Enter date like 01/28/06") If Not IsDate(md) Then MsgBox "Start Over" Exit Sub End If mdate = DateSerial(Year(md), Month(md), Day(md)) For i = 1 To InputBox("Enter number of weeks desired") Select Case Day(mdate) Case 1: x = "st" Case 2: x = "nd" Case 3: x = "rd" Case Is 3: x = "th" Case Else End Select Cells(ar + i, ac).Value = Day(mdate) & x & Format(mdate, " mmm, yyyy") mdate = mdate + 7 Next i -- Don Guillett SalesAid Software "martinkorner" wrote in message ... I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
Thanks to everyone for all your help.
I started by using RD's method. Then tried Don's method, then modified it to get Franz's method! Thansk again, Martin |
Custom fill series?
For me, all offerings failed to provide proper ordinal for days 21, 22, 23,
31 -- David ?B?bWFydGlua29ybmVy?= wrote I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
Need to modify Case statements:
Select Case Day(mdate) Case 1, 21, 31: x = "st" Case 2, 22: x = "nd" Case 3, 23: x = "rd" Case Else: x = "th" End Select -- David Don Guillett wrote If you liked the formula you will really like this. Position the cursor where you want the dates to start. Execute the macro. You will be asked for the first date and the number of weeks desired. Sub datefillcustom() ac = ActiveCell.Column ar = ActiveCell.Row - 1 md = InputBox("Enter date like 01/28/06") If Not IsDate(md) Then MsgBox "Start Over" Exit Sub End If mdate = DateSerial(Year(md), Month(md), Day(md)) For i = 1 To InputBox("Enter number of weeks desired") Select Case Day(mdate) Case 1: x = "st" Case 2: x = "nd" Case 3: x = "rd" Case Is 3: x = "th" Case Else End Select Cells(ar + i, ac).Value = Day(mdate) & x & Format(mdate, " mmm, yyyy") mdate = mdate + 7 Next i |
Custom fill series?
Thanks for catching that. Of course, this is, by far, the easiest way to get
it done. -- Don Guillett SalesAid Software "David" wrote in message ... Need to modify Case statements: Select Case Day(mdate) Case 1, 21, 31: x = "st" Case 2, 22: x = "nd" Case 3, 23: x = "rd" Case Else: x = "th" End Select -- David Don Guillett wrote If you liked the formula you will really like this. Position the cursor where you want the dates to start. Execute the macro. You will be asked for the first date and the number of weeks desired. Sub datefillcustom() ac = ActiveCell.Column ar = ActiveCell.Row - 1 md = InputBox("Enter date like 01/28/06") If Not IsDate(md) Then MsgBox "Start Over" Exit Sub End If mdate = DateSerial(Year(md), Month(md), Day(md)) For i = 1 To InputBox("Enter number of weeks desired") Select Case Day(mdate) Case 1: x = "st" Case 2: x = "nd" Case 3: x = "rd" Case Is 3: x = "th" Case Else End Select Cells(ar + i, ac).Value = Day(mdate) & x & Format(mdate, " mmm, yyyy") mdate = mdate + 7 Next i |
Custom fill series?
Right then...I've got a solution!!
I also realised the problem with 21, 22, 23 and 31. So I made this formula...It's probably far longer than it needed to be, but this works, so I'm happy with it: --------------------------------- =DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11 ,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,2 8,29,30,31},{"st","nd","rd","th","th","th","th","t h","th","th","th","th","th","th","th","th","th","t h","th","th","st","nd","rd","th","th","th","th","t h","th","th","st"})&TEXT(A60," mmmm")&TEXT(A60," yyyy") --------------------------------- It basically specifies the suffix for each individual date. Martin "David" wrote: For me, all offerings failed to provide proper ordinal for days 21, 22, 23, 31 -- David ?B?bWFydGlua29ybmVy?= wrote I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin |
Custom fill series?
|
Custom fill series?
Maybe this is shorter than yours:
=DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"s t",IF(OR(DAY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)= 3,DAY(A1)=23),"rd","th")))&" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy") but it works also with 21, 22, 23 and 31 martinkorner wrote: Right then...I've got a solution!! I also realised the problem with 21, 22, 23 and 31. So I made this formula...It's probably far longer than it needed to be, but this works, so I'm happy with it: --------------------------------- =DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11 ,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,2 8,29,30,31},{"st","nd","rd","th","th","th","th","t h","th","th","th","th","th","th","th","th","th","t h","th","th","st","nd","rd","th","th","th","th","t h","th","th","st"})&TEXT(A60," mmmm")&TEXT(A60," yyyy") --------------------------------- It basically specifies the suffix for each individual date. Martin "David" wrote: For me, all offerings failed to provide proper ordinal for days 21, 22, 23, 31 -- David ?B?bWFydGlua29ybmVy?= wrote I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Custom fill series?
That gives me a small square between the date and the month.
Thanks, Martin "Franz Verga" wrote: Maybe this is shorter than yours: =DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"s t",IF(OR(D AY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)=3,DAY(A1)= 23),"rd","th")))&" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy") but it works also with 21, 22, 23 and 31 martinkorner wrote: Right then...I've got a solution!! I also realised the problem with 21, 22, 23 and 31. So I made this formula...It's probably far longer than it needed to be, but this works, so I'm happy with it: --------------------------------- =DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11 ,12,13,14,1 5,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31} ,{"st","nd","rd","th" ,"th","th","th","th","th","th","th","th","th","th" ,"th","th","th","th","th","th","st ","nd","rd","th","th","th","th","th","th","th","st "})&TEXT(A60," mmmm")&TEXT(A60," yyyy") --------------------------------- It basically specifies the suffix for each individual date. Martin "David" wrote: For me, all offerings failed to provide proper ordinal for days 21, 22, 23, 31 -- David ?B?bWFydGlua29ybmVy?= wrote I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Custom fill series?
It's because Outlook Express cut the formula in the wrong points. Try
copying each line separately: =DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"s t", IF(OR(DAY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)=3,D AY(A1)=23),"rd","th"))) &" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy") martinkorner wrote: That gives me a small square between the date and the month. Thanks, Martin "Franz Verga" wrote: Maybe this is shorter than yours: =DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"s t",IF(OR(D AY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)=3,DAY(A1)= 23),"rd","th")))&" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy") but it works also with 21, 22, 23 and 31 martinkorner wrote: Right then...I've got a solution!! I also realised the problem with 21, 22, 23 and 31. So I made this formula...It's probably far longer than it needed to be, but this works, so I'm happy with it: --------------------------------- =DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11 ,12,13,14,1 5,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31} ,{"st","nd","rd","th" ,"th","th","th","th","th","th","th","th","th","th" ,"th","th","th","th","th","th","st ","nd","rd","th","th","th","th","th","th","th","st "})&TEXT(A60," mmmm")&TEXT(A60," yyyy") --------------------------------- It basically specifies the suffix for each individual date. Martin "David" wrote: For me, all offerings failed to provide proper ordinal for days 21, 22, 23, 31 -- David ?B?bWFydGlua29ybmVy?= wrote I need to have a collumn in my spreadsheet which will display every Sunday's date. e.g. 25TH JUNE 2006 1ST JULY 2006 8TH JULY 2006 etc... I also need the "TH" "ST" ND" and "RD" 's to be in superscript. I am using Excel 2002 (XP) on a Windows XP Home Edition PC. Thanks, Martin -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
| All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com