I have a simple table like this in excel:
The formula i used was
=IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using
LEN, if so ignore, else, convert it to month no.
I have set the custom cell format for
Date Column as
My requirement is as follow:
(1) People can key in the date in two format at the
Date Column. They can key in using "5 Aug 2015" or "5/8/2015". No matter which format they key in, it should disply the date in
dd/mm/yyyy format together with relevant month no in the Month No.
My problem is that whenever i use "5 Aug 2015" format, everything works fine. But when i key in (let's say!) 21/5/2015 (equivalent to 21 May 2015), the
MONTH function stop working. If i key in as
5/21/2015, it work perfectly.
I think this is because of the date and month format. But since I have already set the cell format as
dd/mm/yyyy, I have no idea what is causing the problem.
How do I make sure so that my Date Column accept the input as
21/5/2015 while still giving out the correct month number.