GoogleSheets: calendar that updates automatically when you click either Sunday or Monday

[rachel.louise]

I created a calendar where the dates update automatically based on the Month you select from the drop-down list (i.e the 1st of October falls on a Saturday, the 10th Nov falls on a Thursday, and so on).

The issue I'm having is I would like the whole calendar to update depending on if I select a Monday or Sunday start day for the week from the drop-down list.

I've managed to change the headings (Mon, Tue, Wed, etc.) depending on if you select a Sunday or Monday start day, but I can't figure out how to get all the dates for the month to change to match the start day.

I've attached a sample of my spreadsheet.

Any advice on how to do this would be greatly appreciated.

[AliGW]

Clear your date formulae completely, then in F6:

=LET(s,SEQUENCE(1,7,CHOOSE(WEEKDAY(DATE($C$8,MONTH(DATEVALUE($C$7&" 1")),1)),1,0,-1,-2,-3,-4,-5))+IF(F5="MONDAY",1,0),IF(s<1,"",s))

then in F13 copied down to the other date rows:

=LET(s,SEQUENCE(1,7,L6+1,1),IF(s>DAY(EOMONTH(DATE($C$8,MONTH(DATEVALUE($C$7&" 1")),1),0)),"",s))

[JohnTopley]

Alternative

in F6

=IF(TEXT(DATE($C$8,MONTH($C$7&0),1),"dddd")=F$5,DATE($C$8,MONTH($C$7&0),1),"")

in F7

=IF(F6<>"",F6+1,IF(TEXT(DATE($C$8,MONTH($C$7&0),1),"dddd")=G$5,DATE($C$8,MONTH($C$7&0),1),""))

Copy across to L

[rachel.louise]

Thank you! That worked perfectly.

Also, can I ask a follow-on question here or do I need to make a new post?

My additional question is: Do you know how to make the 'Bills' in the list on the left automatically populate in the calendar based on their 'due date'?
E.g. Internet bill has a due date of 12th August 2022. I would like this to appear in the calendar box for Friday 12th August.

I have highlighted the relevant sections in the sample workbook.

[AliGW]

Wgich of us are you addressing? YOu were offered two different solutions.

Please start a NEW thread suitably titled for your next qquestion.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[rachel.louise]

Apologies, I clicked 'Reply' on your post AliGW so I assumed it linked back somehow.

Both solutions worked, so thank you both.

[AliGW]

I have moved this to the Other Platforms section.

Start a new thread for the details question, which I had prepared a response to.

I have closed your duplicate thread.

[rachel.louise]

Hi,

Apologies for the confusion & thank you for answering my Sunday/Monday query.

I also needed a formula that did the same thing but for Google Sheets instead of Excel (the formula you provided me doesn't work in Google Sheets).

I thought as it was a different program I would need to start a new thread, which is why I asked the question again here in the Other Platforms section. Please advise if I shouldn't have done this.

Also, just to confirm, I should start a new thread for my additional question regarding adding 'bills' to the calendar based on their date?

[AliGW]

Yes, start a NEW thread for the details issue.

Continue HERE for the GoogleSheets version of the question already asked (I have moved this thread).

Your duplicate thread will remain closed. Please review the forum rules on thread duplication.

If you want solutions for GoogleSheets going forward, you should alos update your forum profile to show what you are using.

[AliGW]

Administrative Note:

Oh, dear! I think you need to have a read of our rules, Rachel.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are still new here, I shall do it for you this time, as a courtesy: https://support.google.com/docs/thread/184821582)

[JohnTopley]

See attached as example of how to add data into a calendar (courtesy of Pete_UK)

See Sheet "Activities" and formula in "Calendar"

[AliGW]

@John

I asked the OP to open a new thread for this.

Since you have responded here, then I'll also add the solution I had prepared.

In F7:

=IFERROR(FILTER($B$13:$B$25,$C$13:$C$25=DATE($C$8,MONTH(DATEVALUE($C$7&" 1")),F6),""),"")

Copy paste to the first row under each date.

This may or may not work in GoogleSheets.

[rachel.louise]

@AliGW,

Apologies again. I wasn't aware I was doing anything wrong by posting on another forum.

I can confirm I have now read the forum rules & the article you linked & will do my absolute best in the future to make sure my posts abide by the rules. Thank you very much for your patience & understanding while I'm learning. I really appreciate it.

Your answers have assisted me with my query re Excel & I've received an answer re Google Sheets on the other forum, so I will mark this as solved.

Thank you.