Forum Discussion
DATE format
It seems like the date format on your Mac is providing longer month names in Russian compared to the PC version. To ensure consistency between PC and Mac, you might need to explicitly set the desired custom date format.
Here are the steps to set a custom date format in Excel on Mac:
- Select the cells containing the dates.
- Go to the "Format Cells" dialog:
- Right-click on the selected cells.
- Choose "Format Cells."
- In the "Format Cells" dialog, go to the "Number" tab.
- Choose "Custom" from the Category list.
- In the "Type" field, enter the following custom date format:
ммм-гг
This format should give you a shorter month name followed by a two-digit year.
- Click "OK" to apply the custom format.
If, after applying the custom format, you still see longer month names, you may need to use the English month names in your custom format. In that case, use the following custom format:
mmm-yy
This format uses the standard three-letter English month abbreviation followed by a two-digit year.
Try using these steps to set the custom date format, and it should provide a consistent display on both PC and Mac versions of Excel. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
NikolinoDE Thanks for your help!
I'd like to share more detailed information about my issue with date format in Excel and how I managed to resolve it.
My file contains expenditure data from 2008, with each sheet representing expenses for a specific month in the format "MMM-YY". Consequently, end-of-month balances are recorded in different cells due to varying month lengths.
I had a formula that automatically retrieved the balance from the previous month based on the current month's date, and it worked flawlessly on PC. The formula looked like this:
=@VLOOKUP(ADDRESS(55, DAY(C$1-1)+2,,,TEXT(DATE(YEAR(C$1),MONTH(C$1)-1,DAY(C$1)),"MMM-YY")))
However, on Mac, some month names were shortened to 4 letters, causing the formula to malfunction.
To address this, I discovered that the English language always shortens month names to 3 letters in the "MMM-YY" format. Therefore, I took the following steps:
Manually changed sheet names (a total of 84 sheets, covering 7 years * 12 months) from Russian to English: "янв-23" -> "Jan-23"; "фев-23" -> "Feb-23"; and so on. 😞
Updated the formula everywhere by using automatic replacement, considering the new English format:=@VLOOKUP(ADDRESS(55, DAY(C$1-1)+2,,,TEXT(DATE(YEAR(C$1),MONTH(C$1)-1,DAY(C$1)),"[$-en-GB]MMM-YY")))
These measures ensured the formula's consistent functionality on both platforms.
Thank you very much for your attention and guidance!
Thank you for sharing the details of your issue and the solution you implemented! It's great to hear that you were able to resolve the problem by adjusting the sheet names and incorporating the language code in the formula.
It's not uncommon to encounter compatibility differences between platforms, and your approach of standardizing the month names and using the language code is a clever way to ensure consistent functionality across different environments.
Happy Excel-ing!
