Formulas for year to date, quarterly, and monthly

ashdrap

Hi, Is there a way to do formulas for year to date, quarterly and monthly? And if so, how can I do that formula?

Jeff Reisman

@ashdrap Can you describe your use case a little bit?

We can certainly set up formulas that work off criteria such as year-to-date, quarterly, and monthly, but these need to be set up differently sometimes depending on what data we're working with and what other functions we're using.

ashdrap

Sure, We have a marketing sheet that has "completed projects" and "fire drill projects" We want to be able to count the amount completed monthly, yearly, quarterly and same with the fire drill projects. Does that make a little more sense?

Jaime Ciabattoni

@ashdrap DOH! I think I just deleted my comment. Let's try that again.

We do this to track our marketing campaigns. The campaign requests are put in through a form and then I have a separate sheet that pulls data from that sheet into this for tracking and to be used in a dashboard.

In the below, I'm referencing the "Reference" column because the full names are too long to put into the dashboard charts. The "Month" and "Year" are pulled from the request sheet.

The "Other" is more complicated because I'm using it as a catch all for any campaigns that don't fit into the solutions I am tracking.

Here are a couple of examples of how I pull this into the dashboard.

I know the context is different but I hope it's helpful. Feel free to reach out with any questions!

~Jaime

ashdrap

@Jaime Ciabattoni. Is this something I can do for the formulas? Again, trying to do a count for completed for total of year to date, quarterly etc. I can't quite figure it out with your formula.

Jaime Ciabattoni

@ashdrap

Hoping I can explain this well but follow-up with any questions.

In your main sheet, you are going to need helper columns to define the Quarter (and month if you want it). I made a simplified sample to show you. You'll need to copy the pattern to add in the rest of the months and quarters for both formulas.

THEN you are going to need a separate sheet to count everything. I set one up to add the quarters, but you can use the same method for the months. For the other quarters, just copy the 3 Q1 formulas (one for each status) and change "Q1" to "Q2", etc.

Don't care about the status? Just make it "COUNTIF" instead of "COUNTIFS" and delete {Status}, $Status@row,

The YTD is just using the simple SUM formula.

Let me know if you need more details!!! I'm trying not to do too much at once and overwhelm you. I know when you first start with formulas that happens quickly. You got this.

~Jaime

ashdrap

@Jaime Ciabattoni thank you!! I am going to try it out. I will let you know if I have any questions. So appreciated!

ashdrap

@Jaime Ciabattoni I did the formula for month and it said #UNPARASEABLE

Jaime Ciabattoni

@ashdrap To start: You need commas between the numbers and the "month"...be SUPER careful about the punctuation. One wrong one can break the whole thing...Super frustrating! Hopefully the commas will be enough to fix it.

Sometimes I just start with 2 months for example, make sure it works...then copy and update each month in sets of 2 checking along the way.

I broke these when making them for you so I had to do it that way still! :)

ashdrap

@Jaime Ciabattoni Do I close it out with ) just want to make sure because it still is broken so I want to try it with a few months just to see what I might be doing wrong.

Jaime Ciabattoni

@ashdrap You need a ) for each of the months. If you look really closely in my screenshot, you'll actually see they are color coded which makes things a bit easier to see once you realize it.

Here's a version for 2 months which may make it slightly easier to see...

See how you have IF( for each month? You don't close the statement with ) until the end which is why you need one for each month!

Also, I probably should have done this before (sorry), here's the longer formula just pasted as text so you can see it. I just like how the screenshots show the colors because it makes it a bit easier to follow!

=IF(MONTH(Date@row) = 1, "January", IF(MONTH(Date@row) = 2, "February", IF(MONTH(Date@row) = 3, "March", IF(MONTH(Date@row) = 4, "April"))))

~Jaime

ashdrap

@Jaime Ciabattoni I am sorry for all the questions. THis is what I did and it still came up

Jaime Ciabattoni

@ashdrap Let's go backwards for a second, do you have the Deadline column formatted as a date column?

If so, try copying one of these into your formula bar:

THRU APRIL:

=IF(MONTH(Deadline@row) = 1, "January", IF(MONTH(Deadline@row) = 2, "February", IF(MONTH(Deadline@row) = 3, "March", IF(MONTH(Deadline@row) = 4, "April"))))

THRU FEBRUARY:

=IF(MONTH(Deadline@row) = 1, "January", IF(MONTH(Deadline@row) = 2, "February"))

If the Deadline@row doesn't become color coded when you copy it in, you may need to relink those by deleting each one and retyping them.

ashdrap

It says invalid data type now, Would that be because they didn't originally set up the Deadline as a date column?

Jaime Ciabattoni

@ashdrap I think it's related. The MONTH formula only works off a date. It wouldn't be able to tell what was a month if it was just text. Sorry I didn't think about that at the start.

How is it formatted? Text/Number? Does any field NOT have a date in it? If they are all dates, you should be able to change it, but at this point, it may make more sense to make a copy of your sheet so you can make changes to your SAVED version and not risk messing up the main one. (Also a good practice for the future.)

Then you can keep track of what you have to change and then make the updates in the LIVE version after it's all working.

ashdrap

So I was able to change the deadline to date column with no issues, but the formula is just saying January now.