+ Reply to Thread
Results 1 to 18 of 18

Using a date to return a value

  1. #1
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Using a date to return a value

    Okay, here's what I have. I have a spreadsheet that is imported from Quickbooks Pro 2012, and it comes over with the following date format:

    01/27/2014

    I need a formula that will read that and return the value of 25th. To explain, any invoice that is created between the 1st and the 10th of any given month should return the value 1st, any invoice created between the 11th and the 20th should return 15th, and any value returned between the 21st and 31st should return 25th.

    Where I'm at:

    I'm assuming I'm going to want to use an IF function. Since the digits will always return in the same spot (the 4th/5th spot), I know I want the formula to return the value based on that specific value. I have the following formula that provides similar information/functionality, I'm just not sure how to edit it to return those values.

    //=IF(LEFT(D14,1)="I","INV",IF(MID(D14,3,1)="-","CC","ACH"))

    I have this formula that does something similar to what I want to accomplish, I'm just not 100% how to edit it to find the value that I want, and then return the value that I want. I guess the biggest issue is that I only understand about 85% of what this formula is trying to accomplish.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    Is that 'date' a proper Excel date number or simply text. i.e. does =ISNUMBER(A1) return True or False?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    It returns false.

  4. #4
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Using a date to return a value

    Try this if the data is Text

    =if(mid(A1,4,2)<=10,"1st",if(mid(A1,4,2)<=20,"15th","25th"))

    Following if it is date

    =if(day(A1)<=10,"1st",if(day(A1)<=20,"15th","25th"))
    Last edited by satabp; 02-28-2014 at 02:07 PM.
    Like my answer, then click * below

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    ...assuming it is a date number then

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Using a date to return a value

    wouldnt either
    =CHOOSE(ROUNDUP(DAY(D14)/10,0),5,15,25,25)
    or
    =CHOOSE(ROUNDUP(DAY(DATEVALUE(D14))/10,0),5,15,25,25)
    do?

    edit: as isnumber returns false check first the second one.
    Best Regards,

    Kaper

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    ...and seemingly it doesn't matter that it's text so ignore my concern as to whether it's a 'proper' date number.

  8. #8
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    So far all of those are returning the #value! error. I tried using the formula evaluation tool to determine what the error was, but I couldn't quick figure it out. In one of the solutions provided, it was dividing the numbers, and that was why it was returning the 25th on everything, on the others, I'm not sure if it's reading it as a date or not.

  9. #9
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    The solution I'm going to use is to change the formatting of the date in the A1 column to show just the date and determine the value based on that. The month and year aren't important for what I'm trying to accomplish, so I'll just do it that way. Thanks for all the help guys!

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    Hi,

    I doubt that changing formats will help you. It's what is in the cell not what it looks like.

  11. #11
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    I doubt that changing formats will help you. It's what is in the cell not what it looks like.
    I understand that. What's in the cell is 01/27/2014 and it's not correctly reading the data. If I change it to show just the date, I can ask it to return a value if the number in that cell is >=10, >=20 or other. There's no way to misinterpret that information this way.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    Quote Originally Posted by Jason24 View Post
    I understand that. What's in the cell is 01/27/2014 and it's not correctly reading the data. If I change it to show just the date, I can ask it to return a value if the number in that cell is >=10, >=20 or other. There's no way to misinterpret that information this way.
    At the risk of being pedantic you said originally you were just going to change the format, hence the comment I made. If as you are now implying you are changing the Quickbooks download so that just the day number is brought in rather than a complete mm/dd/yyyy then I'll agree.

  13. #13
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    You are right, I understand your comment more now that I've attempted to fix it. Ultimately what is happening is the formula's are either reading the formula and the first solution being "date" (so, if the formula reads IF(A3,4,2) the A3 is reading as "date" and it is checking the fourth character, which is "e") or or it's reading as what I'm assuming is the value that excel assigns to dates (for example, 41666). Either way, it's not properly reading the characters 27 within the date. I've tried changing it to text, general, etc., but the data that is in the cell will either always read date or 41666.

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    Hi,

    Why don't you upload the workbook so that we can take a quick look. It's often easier when we can see these things in context.

  15. #15
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    I'm not sure if I uploaded it correctly. It's an example, the workbook is exactly the same format, I can't post the exact workbook because it contains sensitive information.
    Attached Files Attached Files

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using a date to return a value

    Hi,

    It seems you already have a solution. The only thing wrong is that the L3 formula refers to A1. It should of course be A3.

    I've also added another formula in M3:M5.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    02-24-2014
    Location
    Texas
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Using a date to return a value

    The one that I had wasn't working, but yours seems to be working perfectly, thanks!!

  18. #18
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Using a date to return a value

    Just to return to concept posted above
    if the date format in column A is the same in this sample file as in original file this (first in my previous post) works:

    Please Login or Register  to view this content.
    returning 25, 25, 1 as copied down in the attachment

    But probably better idea is to have full date in L3 so based on the above test, write in L3:
    Please Login or Register  to view this content.
    and remember to format this cell (after formula is written there) as date (if excel returns something like 41664 there instead of proper date).

    See attachment
    Attached Files Attached Files
    Last edited by Kaper; 03-01-2014 at 04:41 AM.

+ 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. Replies: 12
    Last Post: 10-10-2013, 10:02 AM
  2. Replies: 2
    Last Post: 08-16-2013, 10:18 AM
  3. [SOLVED] Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date
    By gbriscoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 03:29 PM
  4. Replies: 5
    Last Post: 02-10-2012, 12:52 PM
  5. Replies: 1
    Last Post: 06-30-2010, 04:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1