• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Date identification formula

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
SPXHT
Asked:
SPXHT
  • 3
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
SPXHTAuthor Commented:
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
 
SPXHTAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SPXHTAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Pete, I think you meant to close the question, but you probably selected your own comment by accident.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now