Help

Re: Formula For Month if Checked

658 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Estefan_Castro
4 - Data Explorer
4 - Data Explorer

Hello! I am trying to write a formula for tracking what month something was checked as billed. I want to be able to group by month billed to show income by month. Currently I use this Formula for date billed when checked.

IF(Billed=1,TODAY())

This shows me the date it was billed. But for grouping purposes I would like a formula that would say when billed has been checked it will show month checked. I tried this formula

IF(Billed=1,MONTH())

But it does not work, it shows #ERROR!. Is there a formula I can use to state only the month when billed is checked?

4 Replies 4

Hi @Estefan_Castro - if you want to show the month of a date you can use the formula

MONTH({Your Date Field})

This will return an integer, i.e. 1 = Jan, 2 = Feb and so on.

However, this approach you’ve outlined above won’t work. Your formula:

IF(Billed=1,TODAY())

is dynamic, so today, 24th April, it will show “24th April 2020”, but tomorrow, it will be evaluated with tomorrow’s date (as this is now “today”). Your formula doesn’t fix this date in the base. There’s ways to do this with scripts or copy and paste, but, tbh, the easiest way to set this is to have a date field and explicitly set the date the invoice was paid.

Then you can have a “Month Paid” field:

MONTH({Date Invoice Paid}

JB

This Formula seems to be working to mark the month billed by pulling it from the date billed field. But when the month changes will it change or is it static to the date billed field and remain correct to the month?

DATETIME_FORMAT({Date billed},‘MMMM’))

I a very new to formulas sorry! This is my full formula in the field needed

IF(Billed=0,"",DATETIME_FORMAT({Date billed},‘MMMM’))

The use of the above formula is to say that if the billed box is not checked, it will show nothing. otherwise, show the month from the date billed field. Will this work long term? I am also experimenting with “Last modified” to make a date stamp in the date billed section but I cant seem to clear the dates from when I was messing with it today.

Yes, this will work, but you could improve this and remove the need for a checkbox at the same time. You could use:

IF({Date billed}, DATETIME_FORMAT({Date billed},‘MMMM’))

So, in this case, if {Date billed} has a value, then the formula will show the month, otherwise it will be empty. Having the checkbox isn’t really necessary, just filling in the date will be sufficient.

JB

Estefan_Castro
4 - Data Explorer
4 - Data Explorer

The idea i’m going for is simplicity in use. So for some it would be less effort to simply check a box rather than open a calendar the select a date. But the checkbox is also linked to a couple other fields so I figured why not string it into the date stamp and month as well. So the end result is

When I click the checkbox the “Date billed” will update to the current date and time by using the “Last Modified” option. the need for the date and time is so if someone needs to go back through records to find a billed item, they know a date range and time to look for be it in emails or what have you. After it inputs the date and time, the “Month Billed” is updated from the “Date billed Field” The need for month billed, is for grouping by month to track billed totals by month. I then have a price field and amount paid field. So the price is the value of the billable items, the amount paid is to reflect how much we have made so far on said billables. So by checking the box, it adds a date billed, month billed, and amount paid all at once. I could be taking a long walk for a short drink of water lol but I feel like this might work longterm for my company hopefully to track how much we could be making from billables in a month and how much we have made so far and what has been billed on and when