How to find no of Sundays in a Month
Hi, I want to find the count of how many Sundays are there in a month basis on which i would like to find out No of Days in a month - No of Sundays. I have created a daily data entry form in whic...
Forum Discussion
Hi, I think I have a solution in the screenshot below. The formulas are to the right of the fields
Enter any date in field Date2
calculated date field bom-date2= FirstDayOfMonth([Date2])
Day of week-bomdate2 =DayOfWeek([bom-date2])
eom-date2 = LastDayOfMonth([Date2])
days in date2 month=ToDays([eom-date2]-[bom-date2])+1
first sunday of month= Case([Day of week-bom-date2],0,[bom-date2],
1,[bom-date2]+Days(6),
2,[bom-date2]+Days(5),
3,[bom-date2]+Days(4),
4,[bom-date2]+Days(3),
5,[bom-date2]+Days(2),
6,[bom-date2]+Days(1)
)
Sundays =Ceil(ToDays([eom-date2]-[first sunday of month])/7)
Days in month - no Sundays = [days in date2 month]-[Sundays]
------------------------------
Lisa Sell
------------------------------
Enter any date in field Date2
calculated date field bom-date2= FirstDayOfMonth([Date2])
Day of week-bomdate2 =DayOfWeek([bom-date2])
eom-date2 = LastDayOfMonth([Date2])
days in date2 month=ToDays([eom-date2]-[bom-date2])+1
first sunday of month= Case([Day of week-bom-date2],0,[bom-date2],
1,[bom-date2]+Days(6),
2,[bom-date2]+Days(5),
3,[bom-date2]+Days(4),
4,[bom-date2]+Days(3),
5,[bom-date2]+Days(2),
6,[bom-date2]+Days(1)
)
Sundays =Ceil(ToDays([eom-date2]-[first sunday of month])/7)
Days in month - no Sundays = [days in date2 month]-[Sundays]
------------------------------
Lisa Sell
------------------------------
MarkShnier__You
Qrew Legend
Qrew LegendI had this formula in my bag of tricks. This one is for Fridays. I'm pretty sure that if you change the Days(5) to be Days (0), it will count Sundays. ie what goes in the brackets is the Day # of the week starting at Sunday = 0.
var date MyDate = [In Coming Date];
var date MonthStart = FirstDayOfMonth($MyDate);
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) - Days(7)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(0)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(7)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(14)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(21)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(28)) = $MonthStart,1,0)
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
var date MyDate = [In Coming Date];
var date MonthStart = FirstDayOfMonth($MyDate);
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) - Days(7)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(0)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(7)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(14)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(21)) = $MonthStart,1,0)
+
If(FirstDayOfMonth(FirstDayOfWeek($MonthStart) + Days(5) + Days(28)) = $MonthStart,1,0)
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
I've thought of a different approach, no math, for a simpler formula...
All months have at least 4 Sundays minimum. These are the only times months have 5 Sundays:
- first day of month is Sunday and the month has > 28 days.
- first day of month is Saturday and there are 30 or 31 days.
- first day of month is Friday or Saturday and there are 31 days.
So, 2 var statements (# of days and first day of month) then 'if' formulas to determine if it's 4 or 5 Sundays:
var number daysinmonth = Day(LastDayOfMonth([Your Date Field]));;
var number firstdow = DayOfWeek(FirstDayOfMonth([Your Date Field]));
$daysinmonth -
If($firstdow=0 and $daysinmonth>28, 5,
$firstdow=6 and $daysinmonth>29, 5,
$firstdow=5 and $daysinmonth>30, 5, 4)
------------------------------
Bob Sturgeon
------------------------------