Re: Count A certain day in a Mounth

lior_zioni_danc · 08-05-2019 10:47 PM

Hello
1.How can i count a certain day or what a function i need to use for counting for example how many Sunday’s i have in july
2. How can i use it with the google calendar…
For example i have the same math class every Sunday’s in 10:00 pm but there are Sunday’s that it canceled like holidays how can i count it base on the calendar
Tnx

Steve

This might help:

MultiTech

Hmmm… Difficult.

The only way that I can think of accomplishing something like this is to have a table that has a record for every day in the year, and on that table you could have a column that indicates the day number for each of those days. I would also include the month number, and might as well do the week number too - that can make it even easier to do the math.

Then you could run a count, with a select formula, over that table and count the number of zero’s (because Sunday is the day number of zero) limiting so that the records counted are only for that week or below.

Something like:

Count(Select(Year_guide[day_number], and(
[Week_Number] <= weeknum(today()),
[Day_Number] = 0)))

Steve

You can try this expression to identify how many times a given weekday (1-7 for Sun-Sat) occurs in a month containing a given date:

ceiling(
  day(
    eomonth([date], 0)
    - weekday(eomonth([date], 0))
    - ifs(([weekday] > weekday(eomonth([date], 0))), 7)
    + [weekday]
  )
  / 7.0
)

eomonth([date], 0) gets the date of the last day of the month that contains [date].
weekday(eomonth([date], 0)) get the number of the weekday of that last day of the month.
eomonth(...) - weekday(...) computes the date of the last day of the next-to-last week of the month.
([weekday] > weekday(eomonth([date], 0))) asks whether the target weekday ([weekday]) actually occurs before the last week. For instance, if we want the last Thursday, but the month ends on a Monday, the last week does not have a Thursday, so the last Thursday then occurs in the next-to-last week.
... - ifs(..., 7) subtracts one week from the date (see (3)) only if the target day occurs in the next-to-last week (see (4)).
... + [weekday] adds the number of the target weekday ([weekday]) to the given date (...; see (3) & (5)) to get the date of the last target day of the month.
day(...) gets the day of the month from the given date (...; see (6)).
... / 7.0 calculates the (decimal) number of weeks contained in the given number of days (...; see (7)).
ceiling(...) rounds-up the calculated number of weeks (...; see (8)).

This seems to produce accurate results, but I haven’t thoroughly tested it. Use it at your own risk.

GreenFlux

If you make your data source a calendar instead of a sheet, you could use recurring events for each class.

Then create a slice that filters for the classes on the month/day you want to count.

lior_zioni_danc

Continuing the discussion from Count A certain day in a Mounth:

Tnx for all of u… i will try u Suggestions