google docs spreadsheet - inserting programmatic text

Use this forum to discuss issues that are not found in any of the other clerk and stake technology specialist forums.
User avatar
sasgrw
Member
Posts: 271
Joined: Mon Feb 06, 2012 10:47 am
Location: Holly Springs, NC

google docs spreadsheet - inserting programmatic text

Postby sasgrw » Sun Nov 24, 2013 1:35 pm

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
Community Moderators
Posts: 6526
Joined: Sun Mar 16, 2008 11:30 pm
Location: USA, TX

Re: google docs spreadsheet - inserting programmatic text

Postby jdlessley » Sun Nov 24, 2013 5:55 pm

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.
JD Lessley
Have you tried finding your answer on the LDS.org Help Center page or the LDSTech wiki?

russellhltn
Community Administrator
Posts: 20762
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

Re: google docs spreadsheet - inserting programmatic text

Postby russellhltn » Sun Nov 24, 2013 6:33 pm

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.
Have you searched the Wiki?
Try using a Google search by adding "site:tech.lds.org/wiki" to the search criteria.

jdlessley
Community Moderators
Posts: 6526
Joined: Sun Mar 16, 2008 11:30 pm
Location: USA, TX

Re: google docs spreadsheet - inserting programmatic text

Postby jdlessley » Sun Nov 24, 2013 8:40 pm

russellhltn wrote: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.
JD Lessley
Have you tried finding your answer on the LDS.org Help Center page or the LDSTech wiki?

russellhltn
Community Administrator
Posts: 20762
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

Re: google docs spreadsheet - inserting programmatic text

Postby russellhltn » Sun Nov 24, 2013 9:58 pm

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ʺ)
Have you searched the Wiki?

Try using a Google search by adding "site:tech.lds.org/wiki" to the search criteria.


Return to “General Clerk Discussions”

Who is online

Users browsing this forum: No registered users and 1 guest