# Using a date to return a value

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## Re: Using a date to return a value

It returns false.  Register To Reply

4. ## 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"))  Register To Reply

5. ## Re: Using a date to return a value

...assuming it is a date number then

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## 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.  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

9. ## 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!  Register To Reply

10. ## 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.  Register To Reply

11. ## Re: Using a date to return a value Originally Posted by Richard Buttrey 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.  Register To Reply

12. ## Re: Using a date to return a value Originally Posted by Jason24 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.  Register To Reply

13. ## 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.  Register To Reply

14. ## 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.  Register To Reply

15. ## 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.  Register To Reply

16. ## 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.  Register To Reply

17. ## Re: Using a date to return a value

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

18. ## 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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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