I have a simple table like this in excel:

enter image description here

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 asdd/mm/yyyy.

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.

  • 1
    Could it be related to the regional settings in the PC?
    – jcbermu
    Apr 23 '15 at 9:59

Add a validation rule. If the entered value is not in Time/Date format (which usually means it is String) then your formula will default. I agree with @jcbermu, you most probably have issues with regional rules and this is the way to detect whether or not you use valid dates.


As noted the problem is likely related to regional issues. To be more clear, I believe that Excel is storing the date as a number, is expecting to receive the date in the format that is set by your region and displays the date as you prefer by your custom format.

Likely the regional format is set to something other than dd/mm/yy and when you enter 21/5/2015 Excel concludes that it can not be a date since 21 is outside the possible range of 12 months and therefore treats the data as a different type than a date.

A solution is to control the input to force the dd/mm/yy format. Do this with Data validation.

Details can be found at https://stackoverflow.com/questions/47568805/excel-how-to-force-date-as-dd-mm-yyyy-for-data-validaiton

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.