# IF statements to show business quarter

1. ## IF statements to show business quarter

Hello,

I'm new on this forum and I haven't been able to find something has worked.

I have a spreadsheet that tracks all field equipment inspections. This year, certain equipments will need to be inspected twice a year. Ideally, if it was inspected in Quarter 1, it needs to be inspected again in Quarter 3 and if it's Q2 then it'll be inspected again in Q4.

I need a formula that will tell me when the next inspection is due.

This is what I have so far:
Column I (Formula): date of last inspection
Column J (manual entry): "yes" for equipment that will be inspected semi-annually
Column K (formula): The Quarter is was inspected based on Column I's date
Column L (hopefully a formula and where I need help)The quarter the next inspection is due

Thank you!

2. ## Re: IF statements to show business quarter

=date(year(I2),Month(I2)+3,Day(I2))

OR just to show the quarter
=IF(ROUNDUP(MONTH(I2)/3,0)=4,1,(ROUNDUP(MONTH(I2)/3,0))+1)

=IF(ROUNDUP(MONTH(A1)/3,0)=3,1,IF(ROUNDUP(MONTH(A1)/3,0)=4,2,(ROUNDUP(MONTH(A1)/3,0))+2))

so you could test if semi-annual and use

3. ## Re: IF statements to show business quarter

etaf,

I tried your formula but it said it has too many arguments.

I attached a copy of the worksheet i'm working on for you to see.

Thank you!

4. ## Re: IF statements to show business quarter

try this
=IF(OR(I6="",I6="not inspected",J6="suspended"),"",IF(J6="YES",IF(ROUNDUP(MONTH(I6)/3,0)=3,"Q1",IF(ROUNDUP(MONTH(I6)/3,0)=4,"Q2","Q"&(ROUNDUP(MONTH(I6)/3,0))+2)),"Q"&ROUNDUP(MONTH(I6)/3,0)))

I have added some error checking

But it needs lots of testing for different periods

this uses quarters - if you want dates - you can use =DATE(YEAR(I8),MONTH(I8)+3,DAY(I8))
just change the MONTH(I8)+3 to +6 or +12 for , 3 months, 6 months, annually

EDIT
Updated and added every month in cells I11 to I41

5. ## Re: IF statements to show business quarter

Thank you. That worked!

6. ## Re: IF statements to show business quarter

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1