Finding the Weekending Date

[dchaney]

Hello all,

I have a spreadsheet my team uses to track their work for each week. There are 5 Tabs on this spreadsheet, Week 1, Week 2, etc...

I need a formula that will populate Cell G1 on each tab with Sundays date.
Example: Tab 1 for June, Cell G1 would need to have 5 June 2011, Tab 2, Cell G1 would need to have 12 June 2011, etc...

I am drawing a complete blank on this one (Have a case of the Monday's I guess)

 

[barry houdini]

If you just want the first Sunday of the current month for the first sheet then try

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY()))

You can then just add 7 days for that for sheet 2, i.e.

='Week 1'!G1+7 etc.

 

[dchaney]

Ahhhhh your awesome, thanks alot

 

[T. Valko]

Hello all,

I have a spreadsheet my team uses to track their work for each week. There are 5 Tabs on this spreadsheet, Week 1, Week 2, etc...

I need a formula that will populate Cell G1 on each tab with Sundays date.
Example: Tab 1 for June, Cell G1 would need to have 5 June 2011, Tab 2, Cell G1 would need to have 12 June 2011, etc...

I am drawing a complete blank on this one (Have a case of the Monday's I guess)

For the nth Sunday date of the current month:

1st: =CEILING(TODAY()-DAY(NOW())+1-1,7)+1
2nd: =CEILING(TODAY()-DAY(NOW())+1-1,7)+8
3rd: =CEILING(TODAY()-DAY(NOW())+1-1,7)+15
4th: =CEILING(TODAY()-DAY(NOW())+1-1,7)+22
5th: if there is one!

=IF(DAY(CEILING(TODAY()-DAY(NOW())+1-1,7)+29)<8,"",CEILING(TODAY()-DAY(NOW())+1-1,7)+29)

This formula only works if using the default 1/1/1900 date setting.