'Date showing as serial number in formula
I'm currently having an issue with the date format. I have basically extracted the month and year from a standard dd/mm/yyyy date and wish this to be formatted as mmm yyyy ie Jan 2022 for use in a dropdown. This dropdown is then used to filter data using a query formula.
I have broken my sheet down to a 'test' sheet at https://docs.google.com/spreadsheets/d/1ITHMU_ad1LNKWff9q_mHLyb0SaISVyRYunYqA5-ndhU/edit?usp=sharing
You will see the issue I am having in the Dropdown Data Change sheet. The dropdown is in A2 and although it shows the correct format in the dropdown list, in the formula bar it is actually showing as a dd/mm/yyyy format which is then showing as the serial number in the formula I have extracted into D2 - this is then having a negative impact on my query formula in A5 as it is querying against the Jan 2022 formatting.
I have tried the usual formatting options built into sheets as well as attempting to concatenate the data in the Haulage sheet where the data is input and the month and year are extracted but no use so far, so I turn to the experts please?
Solution 1:[1]
instead of your:
=Haulage!M7:M102)
use:
=INDEX(TO_TEXT(UNIQUE(Haulage!M7:M102)))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | player0 |