DAY() function is not working for custom date format

Hmm.

It should automatically recognise it as a date. Are you entering it as such on the worksheet:

A1 = "24/04/2014"
B1 = "=DAY(A1)"

I've just seen the problem. You've created a custom date format in the form "mm/dd/yyyy" and then entered your month as 24.

It is seeing a British format date (i.e. dd/mm/yyyy) but being forced to recognise it as American (i.e. mm/dd/yyyy) and this is what is throwing it, I would guess.

Hi,

This is conflicting, "I have created the custom Date format as "mm/dd/yyyy" e.g. 24/04/2014", if your date format is mm/dd/yyyy, you need to enter 4/24/2014 (month,date, year)

Regards,
Chandra

Please click on ‘ * Add Reputation’ button on the left side bottom of my post if I was helpful in resolving the issue.

dd/mm/yyyy is also in the format list, certainly on my machine.

yes...sorry for the typo.

its "dd/mm/yyyy"....I have updated the question.

Ok, refer to my first answer, if the answer is 'yes', then let us know and we can have a look at the issue.

Also, why the need for a custom format, surely dd/mm/yyyy is already built in as a possible format?

When you enter the date please enter 04/24/2014 and see the result, it works. The cell will display the date in the desired format dd/mm/yyyy i.e, 24/04/2014, also the fucntion DAY(), returns the value.

yes...I tried the same and it is working fine. But the problem is I want to enter the date in the same format i.e. id "dd/mm/yyyy" and it should be displayed as it is. Because the user who are going to use sheet is not as much matured.

Hi friend,

you date is in text format..
i did text to column & made custom format
its working.

Find the enclosed attachment


Hope enjoy,

MaSs

Are you sure? But when I tried to enter date like 25/04/2014 it fails to give day...

You can always force the format of the cell(s) to a dd/mm/yyyy format? I'm not 100% sure I see the problem if you can enter the date in the format you want and the advice I gave above is working?

no its not working bro...I just want to enter the date in dd/mm/yyyy format which is available in other Locales but then the DAY function is not working for them.

If you enter in any cell the text : "22/04/2004" then use =day(CELL) in another cell it will return '22' or (depending on your version of english) '04'.

You need to expand on what you mean, you said above (post #11) that it is working fine, so I can't understand what the problem is! What do you mean by 'other locales'? Is this part of a larger spreadsheet?

Give a list of all the data you are trying to use =day on and I will have a look.

Hi TKCZBW,

Though what you have mentioned in the above post is logically correct, the issue here is when we enter a date in dd/mm/yyyy format (e.g 24/04/2014) and apply DAY () formula on this cell, the result yeilds "#VALUE!". The issue which Milind has pointed out here is during a data entry excercise, when a user/s enter the date in dd/mm/yy format, the formula doesn't work here. It works only when you enter it as 4/24/2014.

I found one solution for this. I added "dd/mm/yyyy" as short format in systems date format available in Language and Regional settings and it worked.
Thanks for you support guys..