Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value 0

**ESE_THE_DON** · 10-08-2021, 02:14 PM

Dear Excel Community,

I have recently build a Marketing Calendar that takes data from a website (nationalday.com) and updates my excel sheet in a calendar look. the index is the date and day of the week (Sun-Sat). For some reason, the data is appearing when I am selected Oct, Nov, Dec - and from Jan-Aug the data is not appearing - it is giving me zero value.

The formila is as follows;

=INDEX('Marketing Index'!B13:H490,MATCH(C4,'Marketing Index'!A13:A389,0),MATCH(B3,'Marketing Index'!B12:H12,0))

=DATE(D2,MONTH(B2),1)

=TEXT(C4,"DDDD")

The data is being pulled from Appended (Queries Povit Table). I have checked the date format and they are all the same. Note the date (Month) is from a drop-down list of months.

I have created a new measure on the Pivot to give me text value instead of number =CONCATENATEX(Append1,Append1[Holiday],", ")

Can anyone help me, I have searched everywhere and I was unable to resolve it. For some reason the Oct-Dec data is correct, however, from Jan-Sep the value is zero.

Thank you in advance for your genuine support.

Best regards,
Ese

**josephteh** · 10-09-2021, 02:45 AM

Because the calendar used for the months Jan-Aug is the year 2022!

**ESE_THE_DON** · 10-09-2021, 03:18 AM

Thank you josephteh for your reply,

Please provide more details, I have double-checked the calendar and it is all on 2021. See Marking Index tab and Jan Tab, they both set to 2021. Also that Tab is set the same. Can you please help me with more information

Thank you in advance for your genuine support.

Ese

**josephteh** · 10-09-2021, 03:25 AM

Go to the sites you linked from and you can check that the months corresponds to the year 2022, or if not, then they must have set up the days of the week wrongly.

https://nationaltoday.com/january-holidays/
https://nationaltoday.com/february-holidays/
https://nationaltoday.com/march-holidays/

etc

**ESE_THE_DON** · 10-09-2021, 08:44 AM

Thank you Josephteh, for the valuable feedback.

Kindly note the “Monthly Calendar” tab is pulling data from the “Marketing Index” sheet and the sheet has 2021 data only. Theoretically, your answer is correct, however, as shown in the “Monthly Calendar” tab the Match (Date Range Jan-21 to Dec-21 & Day of Week) and Index is from the Povit Tabel.

Nevertheless, I have tried to change the January date to 2022 and still, the data is missing. Yes, I have pulled the data from the website, however, my data set is for 2021 as shown in the JAN tab.

Please try to download the file and test – I really need your help.. please

**josephteh** · 10-09-2021, 10:33 AM

Your 01/01/21 in Jan sheet is a Saturday, so Marketing Index picked up 01/01/21 as a Saturday. But your 01/01/21 in Marketing Calendar is a Friday.

Jan sheet.png

Marketing Index.png

Marketing Calendar.png

**Martinlutherz** · 12-28-2022, 06:12 AM

I also want to compile the stats and data and want to share

**lakakhan** · 02-21-2023, 03:36 AM

All the dates normally should turn into those 5 digit numbers (if they were originally formatted as general for example). You simply then need to format them as date with the appropriate format, and your index/match should be fine matching the two date columns.
Findtricks.in

Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value 0

LinkBack

Thread Tools

Rate This Thread

Display

Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value 0

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Re: Match and Index from Date - Oct to Dec Data (Text) is appearing, from Jan to Sep value

Thread Information

Users Browsing this Thread

Similar Threads

Using INDEX MATCH to look across to value, and then match with vertical date value in data

[SOLVED] Find latest date with INDEX MATCH with multiple criteria & partial text

Index Match Match for Data Corresponding to Date

Index Match using Min to find a text value based on the closest date to today

[SOLVED] index and match formula appearing zeo value #N/A

[SOLVED] I need an index match formula to find text in a column and a date in a row.

[SOLVED] Text to column on VBA - Date is not appearing properly

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions