Month to date formula

sahilhq

Hi,

Can anyone please suggest with a "month to date" formula that I can use in my sheet that is set up as shown in the attachment? I have a column that lists down all the dates in the year 2020. I would like to insert the formula in the checkbox column such that if the criteria is month to date criteria is met, the box checks itself. So currently, the box should be automatically checked for 1st April all the way to today's date. When the new month starts, the box should be checked from the starting of the new month and so and so forth. I hope that makes sense.

Genevieve P.

Hi @sahilhq

Ah, understood! My apologies, I thought you wanted April - Today. To adjust this, we just need to take out the " - 1 " from the formula (which is what was making it look back one month, instead of only this current month).

My two criteria are now that the MONTH in the Date column is the same MONTH as Today's Month, and that the Day is earlier, or equal to, Today.

Try this:

=IF(AND(MONTH(Date@row) = MONTH(TODAY()), Date@row <= TODAY()), 1, 0)

To answer your second question, yes! This will update based on Today's month... so once we start in on June, it will only check boxes that are in June, earlier or equal to Today's day.

Keep in mind that we are using the TODAY formula though, which needs the sheet to be accessed in some way to recognize what date Today is, to then update the sheet. There is a list of ways to make sure the sheet is accessed so Today will update in this Help Center article: https://help.smartsheet.com/function/today

Let me know if I can clarify anything further.

Cheers,

Genevieve

Genevieve P.

Hi @sahilhq

If I understand you correctly, you want the checkboxes to be checked if the month is LAST month, and any of the dates from THIS month, up until Today. Is that correct?

If so, try this:

=IF(AND(MONTH(Date@row) >= MONTH(TODAY()) - 1, Date@row <= TODAY()), 1, 0)

This will check the box for anything in the last Month, which is indicated by MONTH(TODAY()) - 1. Then it will also check the box for anything in this current month, as long as it is before or equal to Today.

Here are some Help Center articles I used for this: MONTH function / TODAY function / @row function / IF function / AND function

Let me know if you have any questions, or if I misunderstood what you were looking to do.

Cheers,

Genevieve

sahilhq

@Genevieve P Many thanks for the response. Actually, I would like the checkboxes to be checked if it is in this month only, up until today. I don’t want any data from the last month to be shown.

Also, once the new month starts, will this formula be able to detect that the new month has started and then for example on the 4th June checks the corresponding checkboxes from 1st June - 4th June ONLY?

Thanks once again!

Genevieve P.

Hi @sahilhq

Ah, understood! My apologies, I thought you wanted April - Today. To adjust this, we just need to take out the " - 1 " from the formula (which is what was making it look back one month, instead of only this current month).

My two criteria are now that the MONTH in the Date column is the same MONTH as Today's Month, and that the Day is earlier, or equal to, Today.

Try this:

=IF(AND(MONTH(Date@row) = MONTH(TODAY()), Date@row <= TODAY()), 1, 0)

To answer your second question, yes! This will update based on Today's month... so once we start in on June, it will only check boxes that are in June, earlier or equal to Today's day.

Keep in mind that we are using the TODAY formula though, which needs the sheet to be accessed in some way to recognize what date Today is, to then update the sheet. There is a list of ways to make sure the sheet is accessed so Today will update in this Help Center article: https://help.smartsheet.com/function/today

Let me know if I can clarify anything further.

Cheers,

Genevieve

sahilhq

@Genevieve P That worked! Thank you so much :)

Genevieve P.

No problem at all! Happy to help.