google docs spreadsheet - inserting programmatic text

[sasgrw]

I haven't checked the google docs forums yet but does anyone know if this is possible with a google docs spreadsheet? My spreadsheet has the Sunday date in the first column, 5Jan14, 12Jan14, etc. I have a real date in the first cell and all the cells under it have 7 (days) added to the previous cell so I only have to put one date in.

Anyway, that's sort of beside the point. In the next column, I want to insert either WC (ward council) or PEC depending on whether the date is the 1st, 3rd, or 5th Sunday (WC) or the 2nd or 4th Sunday (WC).

So I want my cell formula to take the date and figure out which Sunday of the month it is and insert the appropriate text. Does that make sense?

[jdlessley]

The date functions (like DATE, YEAR, MONTH, and WEEKDAY) available in other spreadsheet applications such as MS Excel needed to accomplish what you want are not available in Google Docs.

In testing I found it was faster to fill in the text values WC and PEC for a five Sunday month and then copy and paste into the appropriate cells for each month.

[russellhltn]

You don't need date fuctions - just text parsing functions.

1-7 is the first
8-14 is the second
15-21 is the third
22-28 is the forth
29-31 is the fifth.

[jdlessley]

You don't need date fuctions - just text parsing functions.

The values he will be working with will be dates, numeric values, and not text. I don't see how to get the day of the month from a date without something like the DAY function of Excel.

Perhaps you can provide an example of how he can use what is available in Google Docs to do what you suggest.

[russellhltn]

Fortunatly, it seems Google docs does have a DAY function. I just tried this and it worked fine to say what "week" it was.

=Int((Day(A2)+6)/7) where A2 is the date in question.

Here's a formula to give WC for 1,3,5 and PEC or 2,4. There's a typo in the OP, so I'm not 100% sure what was requested, but it's easy enough to flip them.

=If(Mod(Int((Day(A2)+6)/7),2)=0, ʺWCʺ, ʺPECʺ)