Date Serial Number Change

[mangoliusz]

Hi

I am currently trying to set up two data filter criteria for my spreadsheet. I am using the format: =">=" &DATE(2017,1,1)
on one side but it shows up as >=42736 which is the serial number. I tried formatting it but it won't budge.

Is there a way around this where I can format the dates? Otherwise when people use the spreadsheet, it's going to be the serial number (which works) but just looks really bad in all my graphs as well as my table. If there is also a better way to do this, I am open to fixing this as well

Thanks

 

[shg]

Filtering how? Shows up where?

 

[mangoliusz]

I am using an advanced filter with 2 other criteria but I also want to filter by dates. I am using say cell C2 as one date and D2 as the other date and then using greater thans and less thans to filter between. The data then gets filtered on the same page.

My main problem is that I want to do this by MM/YYYY as my data is all by month so there is no point doing it by day. However because I am terrible at Excel, I am trying to start from the beginning with just a DD/MM/YYYY format but it keeps displaying the serial number. Is there a way to get rid of this formatting and format it so I can display MM/YYYY?

In my full spreadsheet, the dates in there are in the form of 01/01/2016, 01/02/2016, 01/03/2016 - DD/MM/YYYY format. Sorry i am a bit of an excel newbie so any tips would be appreciated.

Thanks

 

[Guru008]

Hi,

Try this

=">=" & TEXT(DATE(2017,1,1),"YYYY-mm-dd")

 

[mangoliusz]

Thanks for that, it does display what I intend

My problem now is say my Cell C2 is : =">=" & TEXT(DATE(2016,11,1),"YYYY-mm") and my D2 is ="=<" & TEXT(DATE(2017,1,1),"YYYY-mm") so in this case, I am intending to filter between November and January, but nothing happens. Would you know why?

Thanks again

 

[eduzs]

Formating the cells with date "DD/MM/YYYY" date format does not work for you? (righ click cells, "format cells", number, date.)
You also can use the AutoFilter to filter a range of data by MM and YYYY.

 

[mangoliusz]

Yup it doesn't work. Also i just tested that putting in the TEXT within the formula above makes the whole thing fail to work.

 

[mangoliusz]

So if i get rid of the formulas, i can change the format but as soon as i put anything with <= etc., formatting it wont budge