nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Logic Help

jjg279

I am trying to come up with a formula that will change my "Has been billed" column to Yes or No based on the dropdown choices in one column and the created date, I am having trouble coming up with some logic that can work around the year with sporatic billing periods. Here is my current formula:

I either have remnants from last year (March this year should be red):

But also can't just use the year marker as part of my countifs I don't think (because there is still some "last year" that needs charged):

Anyone got a clever solution?

Find more posts tagged with

Formulas

Accepted answers

LesGos

Do you only want to count if it has been billed This Year? In your example, since March 25 has been billed but March 26 has not been billed, and you want it to say no / turn red, it sounds like you're only wanting to count 2026 billing, is that correct?

I think the way to do this would be an IF statement based on TODAY and the month(s) selected, which I can help write once you've responded to the above.

jjg279

Aloha @LesGos and @Georgie , your points are what I was struggling over. I do only want to count if it has been billed "in this year" but it isn't attached (necessarily) to the numerical year. Our billing person pushes these charges through every 3-9 months so there could be an instance - like above - where we are billing for December 2025 in March of 2026, and I need December 25 to turn red. And I thought of trying to use some IF statement for when the "billed" row was created, but the date range is so wide.

All comments

Paul Newcome

Can you provide more details? How do you decide whether or not something has been billed or still needs to be billed? What does the year have to do with it? How do the selections made in the dropdown column impact the need?

jjg279

Hey Paul, this column was for filtering for a report to put on a dashboard. I have summary information in the rest of the sheet gather data from forms for the last 11 months (to avoid overlap).

My billing person would then select which months they billed for, and the reports would turn green. My problem is the green isn't going away fast enough. They billed for "March" on July 14th of 2025 - and while my totals to be billed for the month of March are accurate for 2026, they are listed as already billed based on my formula logic. Trying to come up with a way to draw a line that encompasses the wobble between date used and date charged; does that help?

Georgie

Hi @jjg279,

It looks like the issue is that rows are created after the month to be billed, meaning that the created date for those rows is within the last 11 months.

I would suggest creating a helper column with the year listed, and you can then use that column in your COUNTIFS formula to ensure you’re counting rows in the correct year.

Additionally, does the user also enter the year when they enter the month to be billed? If the year isn’t already being input with the month, it should be, so that each row can be counted for the correct year.

Hope that helps!

Georgie

LesGos

jjg279

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.