IF formula combined with dates

Hello,

I need help. I am wondering how can I create a formula using the IF function with a range of dates. For example:

If the date is between 01/01/2018 and 03/30/2018, then write 1st Quarter., and if the date is between 04/01/2018 and 06/30/2018, then 2nd. Quarter, etc....

Thank you in advance!

Find more posts tagged with

Formulas

Comments

Paul Newcome

Give this a try (a few minor changes may be needed, explained below)

=IF(AND(Date@row >= DATE(2018, 1, 1), Date@row <= DATE(2018, 3, 30)), "Q1", IF(AND(Date@row >= DATE(2018, 4, 1), Date@row <= DATE(2018, 6, 30)), "Q2", IF(AND(Date@row >= DATE(2018, 7, 1), Date@row <= DATE(2018, 9, 30)), "Q3", IF(AND(Date@row >= DATE(2018, 10, 1), Date@row <= DATE(2018, 12, 31)), "Q4"))))

Where it says Date@row, you only need to change the Date portion to your column name.

"Q1", etc... can be changed to "1st Quarter", etc... or whatever you would like it to say.

The dates that denote the beginning and end of each quarter can be changed to whatever you would like. The format for Smartsheet is DATE(YYYY, MM, DD)

J. Craig Williams

This formula does the same as Paul's example:

="Q" + INT((MONTH(Date@row) + 2) / 3)

without the year.

If it needs to say "1st Quarter" and not "Q1", then you'll need an nested if like Paul's (with the shorter formula to determine 1-4 here) or a LOOKUP() function to return "1st" when search for "1"

The problem with building formulas that look for a specific time frame is that they will need to be changed / maintained as time moves forward.

On January 1st, all dates will have a blank Quarter number.

I'd prefer

=IFERROR("Q" + INT((MONTH(Date@row) + 2) / 3) + "Y" + RIGHT(YEAR(Date@row),2),"not a date")

to return Q1Y18 for 1st quarter this year.

My $0.02

Craig

Paul Newcome

Diggin' it...

I always forget about the INT function.

Silvia C Rodriguez

Thank you all!

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…