Solved

Date format changed to text format

Posted on 2013-12-14
3
322 Views
Last Modified: 2013-12-14
Folks,
I would like to use the function offered to me below. In cell C2 I enter my date as text, 11/27/2013, In cells A3:A16 I have dates also formatted as text, same as in C2. Cell D3 holds the product number also formatted as text. The same formatting also applies to B3:B16, text.

{=SUMPRODUCT((DATEVALUE(D2)=$A$3:$A$16)*(D3=$B$3:$B$16))}

The result is #VALUE error.
0
Comment
Question by:Frank Freese
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39718875
Is this literally just because your date is in C2 but your formula is actually referencing D2?
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39718877
Also I think you need a DateValue around your range in col A:
{=SUMPRODUCT((DATEVALUE(D2)=DATEVALUE($A$3:$A$16))*(D3=$B$3:$B$16))}

Open in new window

0
 

Author Closing Comment

by:Frank Freese
ID: 39718881
Great job! You nailed it there....
Thank you
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question