Recent Questions

I am using a INDEX, MATCH function in sheet Car Search to pull data from sheet Raw Data. One of the cells is Date Purchased, this will show the date in format dd/mm/yyyy (UK Format). The problem is, when I enter a date in this format, it doesn't correctly pull over to the Car Search sheet. Instead, it shows up 42350 when I entered 12/12/2015 in the Raw Data sheet. Both cells have been formatted to Date, but it is still showing up as 42350 and not 12/12/2015. Is it because the function is preventing it from showing date format? Anybody know why Excel does this? I have tried on a new sheet, but same result. Any help much appreciated :) Please see images below. Car Search Sheet Raw Data Sheet L...

According to https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e?ui=en-US&rs=en-US&ad=US&fromAR=1, the built-in formats starting with an asterisk (*) are regional. They will display different format according to your OS region. I specify a custom format dd/mm/yyyy hh:mm in a cell, the first weird thing is, Excel treats it as a built-in format, id is 22. According to ISO/IEC 29500-1, Information technology — Document description and processing languages — Office Open XML File Formats — Part 1: Fundamentals and Markup Language Reference, 18.8.30 numFmt (Number Format). ID 22 is m/d/yy h:mm The 2nd weird thing is, the built-in format whic...

I'm using data coections in excel to link to data exported from another software package. The analysis I do relies on dates captured by the software and it is very helpful to group by the dates and doing other operations on it. The spreadsheet I created last week worked perfectly and Excel handled the dates as it should. When I updated the coection this week, all the groupings in my pivot tables are messed up because Excel just treats the date string as text. I have not changed anything in the spreadsheet apart from updating the coection. The date column in the exported spreadsheet is automatically formatted correctly. Nothing major has changed in the past week except for a few extra lines o...