+ Reply to Thread
Results 1 to 6 of 6

IF statements to show business quarter

  1. 03-11-2014, 11:16 AM #1
    apmi
    apmi is offline
    Registered User
    Join Date
    03-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    40

    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!
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 03-11-2014, 11:27 AM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: IF statements to show business quarter

    perhaps add a spreadsheet here, rather than an image

    you could add three months

    =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)

    this adds two quarters
    =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
    Last edited by etaf; 03-11-2014 at 11:31 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 03-11-2014, 11:37 AM #3
    apmi
    apmi is offline
    Registered User
    Join Date
    03-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    40

    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!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-11-2014, 11:55 AM #4
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    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
    Attached Files Attached Files
    Last edited by etaf; 03-11-2014 at 11:57 AM.
    Reply With QuoteRegister To Reply
  5. 03-11-2014, 12:25 PM #5
    apmi
    apmi is offline
    Registered User
    Join Date
    03-11-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: IF statements to show business quarter

    Thank you. That worked!
    Reply With QuoteRegister To Reply
  6. 03-11-2014, 12:42 PM #6
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: IF statements to show business quarter

    cool, your welcome

    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Will automatically show the quarter depending on the date
    By Elainefish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2013, 04:23 AM
  2. Macro to show date range from 'custom' column (Quarter 1) from list of dates
    By danlRB80 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2013, 09:16 AM
  3. Need help formatting small business (web design business) related spreadsheets
    By ramone_johnny in forum Excel General
    Replies: 1
    Last Post: 09-14-2012, 02:18 PM
  4. Last business day of month function +1,2,3,4 business days
    By dstock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2008, 06:08 PM
  5. WHERE CAN I GET A BUSINESS PROPOSAL OR A BUSINESS EXPENSE SHEET?
    By FELICITA ROSALES in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-18-2005, 05:27 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1