# 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.

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?

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

It returns false.

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

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

...assuming it is a date number then

Formula:

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.

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.

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.

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!

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.

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.

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.

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.

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.

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.

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.

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!!

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

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:

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:
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

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