+ Reply to Thread
Results 1 to 8 of 8

Date in Text format within a if statement

  1. #1
    Registered User
    Join Date
    11-10-2016
    Location
    Ireland
    MS-Off Ver
    Office365
    Posts
    4

    Date in Text format within a if statement

    a1 = number - a2 = the date error message appears to have issue with ( after the use of TEXT?

    =IF(A1>0;"OMRU Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""); " ")


    also - if cell has a value and that value can be changed - how do I write a formula to add a particular range ie cell value is 3 - the sum of A1.c1 - cell value is 6 - A1.f1 - cell value is 9 - a1.p1 )

    please help

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Date in Text format within a if statement

    I don't understand how a value of 9 equates to column P, but I think you are looking for something like this:

    =IFERROR(SUM(INDIRECT("A1:"&CHAR(A2+64)&"1")),"")

    where A2 contains the number of columns you want to sum (max 26, which is equivalent to column Z).

    For your first part, the TEXT function is referring to cell A2, but A2 contains the formula. It would help if you gave us a bit more detail.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-10-2016
    Location
    Ireland
    MS-Off Ver
    Office365
    Posts
    4

    Unhappy Re: Date in Text format within a if statement

    Thanks for the reply - possibly did not explain the requirement clearly -
    p should have = I
    What I am trying to achieve is a formula that will add a number of cells - depending on the value a particular cell ie
    the value cell is 3 - then the formula will add cells a1+a2+a3, if the cell value is 6 it will add a1+a2+a3+a4+a5+a6 etc

    I have created this formula in the past - using if statement just cant recall


    =IF(A1>0;"OMRU Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""); " ")

    What I am trying to achieve is that when cell A1 is numeric ( number of months ) - the value is inputted within the statement & then I am looking at
    getting the date into the statement - that value would be inputted in cell a2 ---
    If I use the "&A2"& - the result is numeric 42674 :-(

    I tried to format cell a2 as text but still get an error

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Date in Text format within a if statement

    I would have thought that in Ireland you would use a comma rather than a semicolon to separate parameters in a formula, so try it this way:

    =IF(A1>0,"OMRU Accounts for the "&A1&" months ended"&Text(a2,"DD-MM-yyy""), " ")

    where A1 contains the number of months and A2 contains the date - the formula should be in a different cell.

    As regards the other query, you now seem to want to add up cells in a column (i.e. vertically) rather than across a row - which is it to be?

    Pete

  5. #5
    Registered User
    Join Date
    11-10-2016
    Location
    Ireland
    MS-Off Ver
    Office365
    Posts
    4

    Re: Date in Text format within a if statement

    Pete

    I did try comma - tried alot of variations - nothing prevailed - your suggestion is not working - same error
    query 2
    Early morning have to wait for coffee to kick in - is should have read columns - A1+b1+c1+d1 etc

    thanking you

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Date in Text format within a if statement

    Well, the first formula I gave you should work across columns.

    It would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Pete

  7. #7
    Registered User
    Join Date
    11-10-2016
    Location
    Ireland
    MS-Off Ver
    Office365
    Posts
    4

    Re: Date in Text format within a if statement

    pete

    file is attached I hope !! sheet 1 has the date formula regarding the date cell B1 - sheet 2 is where the other formula is

    thanks

    Peter
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Date in Text format within a if statement

    In B1 of the first sheet you should have this formula:

    =IF(A1>0,"OMRU Accounts for the "&A1&" months ended "&TEXT(A2,"dd/mm/yyyy"))

    I'm not exactly sure what you want in the second sheet, but if you want to sum the number of months given in V1, you can have this formula in V2:

    =IFERROR(SUM(INDIRECT("E"&ROW()&":"&CHAR(V$1+64+5)&ROW())),"")

    This will include column E (B/F value) and add the first 3 months to it. You can copy the formula into U2 to get 6 months added onto E2. If you don't want to include the E value then change it to F. Then you can copy both formulae down.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dates in Text and Date format, need help converting all to date format...
    By Branbran10 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-14-2016, 12:21 PM
  2. [SOLVED] Convety Text date format to a (numbered) date format
    By namluke in forum Excel General
    Replies: 1
    Last Post: 08-20-2014, 05:58 AM
  3. Replies: 8
    Last Post: 07-31-2014, 07:53 PM
  4. Cannot convert date (which is in text format) to date in numeric format
    By geniuspro in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-19-2012, 09:21 AM
  5. If statement using date format as logical_test
    By daveeeo in forum Excel General
    Replies: 5
    Last Post: 05-30-2010, 10:52 PM
  6. [SOLVED] How to add date format into if statement?
    By Eric in forum Excel General
    Replies: 4
    Last Post: 11-05-2005, 11:45 AM
  7. How to add date format into if statement?
    By Eric in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2005, 11:45 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1