Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date identification formula

Posted on 2012-08-20
6
Medium Priority
?
346 Views
Last Modified: 2012-08-22
Would like to write a formula that identifies the MS# in the formula sheet in the Raw Data sheet and brings back the date the Raw Data sheet is forecasting billing under the "month".  I have one showing you I have found it, and put the date I would want it to fall in, obviously not this formual is the answer.  That would be terribly time consuming.  :)

Thanks,

Pete
Test-File.xlsx
0
Comment
Question by:SPXHT
  • 3
  • 3
6 Comments
 
LVL 50
ID: 38314141
Hello,

this would be a lot easier if the data types used in the spreadsheet were the same. On the Formula worksheet, you are using real dates in row 1, but on the Raw Data sheet the "dates" are really text. To convert them to dates, insert an empty row before row 1, then enter this formula into cell D1

=DATE(RIGHT(D2,4),SUBSTITUTE(LEFT(D2,2),"/",""),SUBSTITUTE(MID(D2,FIND("/",D2)+1,2),"/",""))

Copy the formula across, copy the results and paste as values into row 2. Now delete row 1 and format the dates with your preferred date format.

Now onto the formula worksheet. The MS# is a number, but the corresponding values in the Raw Data sheet are stored as text. This does not have to be converted, though, since a Sumproduct formula can cater for it. In cell D2 use this formula

=SUMPRODUCT(('Raw Data'!$A$3:$A$130=TEXT('Formula worksheet'!$B2,"0"))*(MONTH('Raw Data'!$D$1:$AZ$1)=MONTH('Formula worksheet'!D$1))*(YEAR('Raw Data'!$D$1:$AZ$1)=YEAR('Formula worksheet'!D$1))*'Raw Data'!$D$3:$AZ$130)

Copy across and down. You may want to format zero values not to show, using a custom format.

See attached.

cheers, teylyn
27835685-Test-File.xlsx
0
 

Author Comment

by:SPXHT
ID: 38314855
Teylyn,

This looks correct.  I just got in tonight and am going to call it a night.  I have some additional requirements if its not to much of a trouble.  I will send it to you 1st thing in the morning.

Thanks again,

Pete
0
 

Author Comment

by:SPXHT
ID: 38316735
Teylyn,

Could you help me write the formula from the Raw Data tab in the highlighted yellow section.

Thanks,

Pete
27835685-Test-File.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SPXHT
ID: 38317952
I've requested that this question be closed as follows:

Accepted answer: 0 points for SPXHT's comment #a38314855

for the following reason:

Job well done!<br /><br />Pete
0
 
LVL 50
ID: 38317953
Pete, I think you meant to close the question, but you probably selected your own comment by accident.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38318023
Try in C2 and copy down

=IFERROR(INDEX('Raw Data'!$D$1:$AZ$1,MATCH(99^99,OFFSET('Raw Data'!$D$1:$AZ$1,MATCH(TEXT(B2,"0"),'Raw Data'!A:A,0)-1,0),1)),"")

See attached.

cheers, teylyn
27835685-Test-File.xlsx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 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