Formula Q: view rows for a particular month

#1

Hiya folks! I think this is an easy one. I have a doc with a date column called “Invoiced” and I want to create a view that shows me just rows that have an Invoice dated December.

I created a View on my doc, and then added a Filter using the “Month” formula (see below), thinking that maybe this is the right formula to use, but it’s not working.

Any help would be most appreciated. There might be, I suspect, a much simpler way to do this.

-Dana

#2

Oh, might help to show in which way it’s not working, eh? I get a reduced view of my data, instead of all the data in the initial view, but the visible rows (after applying the formula) are empty. (Plus, isn’t showing only Q4, which I would expect; I have four quarters of data for the year):

#3

Hi @Dana_Armstrong , can you show what data is in Invoiced - Month returns a number between 1-12. Is Invoiced the same or is it a date? Invoiced.Month = 12 might be the filter you are looking for.

For what groups show - if you rt click on quarter you will see a “arrange and manage” option. That lets you pin groups so they show even when empty. These two groups might be pinned.

#4

Hi Mallika,
Thanks for responding to my question!

My Invoiced column contains dates, like this:

image

Using Invoiced.Month=12 as you suggested didn’t work for me. (Didn’t seem to do anything at all.) Maybe there’s a different syntax for that? In any case, I’d probably need to specify a year, not just a month.

Coda suggests these items for me to use, none of which seem quite right for an entire month:

Any other suggestions for formulas to try?

-Dana

#5

Hi @Dana_Armstrong,

What Mallika is asking is for the type of data that you have in the “Invoiced” column.

For instance…

Invoiced Column = “This is an Invoice”, is a string and there isn’t any date available.
Invoiced Column = “4027”, this is an invoice number and there is no date
Invoiced Column = “03/25/2018”, this is a date and we can extract the month or year as needed

Can you let us know what values you have there and what type of column it is? When you hover over the column, you’ll see the column type. How you have the Invoiced column listed steers how we extract the date.

1 Like
#6

If you create a new column and call it “Month”, then add a formula like this…

=Invoiced.Month()

That should give you the month number. Then you can use this column instead.

You can also do the same thing for “Year”, then you have “Month” and “Year” that you can use for filters.

2 Likes
#7

If Invoiced is a Date then this filter will do the job too -
Invoiced >= ToDate(12/1/2018) And Invoiced <= ToDate(12/31/2018)