Converting date to US month format

[Ozwilly]

Hi,

I have been using a formula to convert a date to a relevant month. As I am based in Australia, we would write 3rd October 2011 as 3/10/11. I have an IF statement I use which, if 3/10/11 was in A1, would be:

=IF(A1="","",TEXT(A1*29,"mmmm")) This would return the value 'October' in the relevant cell.

I now want to be able to use this formula with the US date format, which for 3rd October 2011 would be 10/3/11, however in its current format the formula returns the value 'March' instead of 'October'.

Appreciate any help!

[Leith Ross]

Hello Ozwilly,

Provide the value in A1 is a Date and not Text you can extract the month correctly for Australia or the US this way.

=IF(A1="","",MONTH(A1))

[Ozwilly]

Thanks, I have tried it and it is not working. I have attached an example worksheet (Excel 2007). What I am getting is a number, not a month, and am not sure how to format it. The number however is not correct for US as if the resulting '3' was converted into the month of the year it would be March not October.

[Leith Ross]

Hello Ozwilly,

Sorry about that, I forgot that returned only the serial number of the month. This will return the full month name.

=IF(A1="","",CHOOSE(MONTH(A1),"January","Februrary","March","April","May","June","July","August","September","October","November","December"))

[Ozwilly]

Hi, I posted a reply the other day but seems not to have stuck Unfortunately this formula is not working. If you refer to the attachment, I am trying to reflect the date 5th October 2011 in both Australian and US formats to show the relevant month.

Using the formula, if I put in 5/10/11 for Australia, it correctly shows the month being October. In the US however 5/10/11 would be 10th May 2011 and I want the formula to show this to be the case. As I need to share this model with someone in the US and for it to work for them with the correct date format, is there something I can do in the formula or the Excel settings to ensure it is working correctly for either country? Even if I was able to save two copies, one being US specific and one being Australian specific that could work, however I am not sure how to achieve this?

Thanks

[Ozwilly]

Hi,

I posted a couple of weeks back however unfortunately have not had a reply that is solving my problem. I thought I would repost the question in case someone else can assist me. What I am trying to do is to show a date keyed in, as the correct month using TEXT. It needs to match both US & Australian formatting for dates - hopefully the information below will make it clearer

If you refer to the attachment, I am trying to reflect the date 5th October 2011 in both Australian and US formats to show the relevant month.

Using the formula, if I put in 5/10/11 for Australia, it correctly shows the month being October. In the US however 5/10/11 would be 10th May 2011 and I want the formula to show this to be the case. As I need to share this model with someone in the US and for it to work for them with the correct date format, is there something I can do in the formula or the Excel settings to ensure it is working correctly for either country? Even if I was able to save two copies, one being US specific and one being Australian specific that could work, however I am not sure how to achieve this?

Thanks

[Ozwilly]

Hi,

I posted a couple of weeks back however unfortunately have not had a reply that is solving my problem. I thought I would repost the question in case someone else can assist me. What I am trying to do is to show a date keyed in, as the correct month using TEXT. It needs to match both US & Australian formatting for dates - hopefully the information below will make it clearer

If you refer to the attachment, I am trying to reflect the date 5th October 2011 in both Australian and US formats to show the relevant month.

Using the formula, if I put in 5/10/11 for Australia, it correctly shows the month being October. In the US however 5/10/11 would be 10th May 2011 and I want the formula to show this to be the case. As I need to share this model with someone in the US and for it to work for them with the correct date format, is there something I can do in the formula or the Excel settings to ensure it is working correctly for either country? Even if I was able to save two copies, one being US specific and one being Australian specific that could work, however I am not sure how to achieve this?

Thanks

[daddylonglegs]

If you have real dates in a spreadsheet then opening the worksheet in a different region shouldn't cause any problems - the date won't change.

If you type in a date 5/10/2011 then that is recognised as an October date because your regional settings assume dd/mm/yyyy.....but if you send the workbook to somebody in the US normally the date will look different - it'll show as 10/5/2011, it'll still be October 5th

[you can check that by changing your own Regional settings temporarily to US]

...and the text formula will still show October...

Note this is simpler to get the month as text

=TEXT(B3,"mmmm")

[ConneXionLost]

Ozwilly,

This was a duplicate post and as such does not comply with Rule 5 of our forum rules.

These threads have been merged. Please do not create duplicate posts.

[Ozwilly]

Brilliant ! Thanks so much