I trust that you found value in this video. Feel free to post your questions and feedback in the comments below. I read and respond to every comment. Have a fantastic day. Jason
Unfortunately, even if my country is set to Aus, or South Africa, and I then set the format to dd-mmm, and then enter 12/10 (in other words, hey excel, I am typing the day first, then the month), this wonderful software converts it to 10-Dec :(
Hi Hennie. This is a common problem. It's an issue with your computer's settings, not Excel. Here's what to do: 1. Click the Windows Start button and type "Region". 2. In the results, choose Region Settings. 3. Under the Region heading , make sure that the "Country or region" and the "Regional format" are set correctly. One or both probably say US right now. 4. While you're there, click "Time & Language" in the breadcrumbs at the top of the page, then choose "Date & time". 5. Make sure the time zone is correct. 6. Close Excel down and restart for the new settings to come into effect. Let me know how you go. Jason
Thanks for the video. I'm looking to represent a date in Fiscal year format. For example 06/31/2019 should show up as FY20. I was able to show it as FY19, could we do any mathematical operations like yy+1 to achieve this?
Can't be done with a format but can be done with a formula. I'm going to use Australia as an example. The financial year ends on 30 June, so any date between 1-Jul-20 and 30-Jun-21 is FY21. I have an example here: officemastery.com/example/FY.xlsx. Cell F2 contains the month number of the last month in the financial year. In Australia, that's 6. For a date in A1, the formula to determine the financial year is: ="FY" & IF(MONTH(A1)
You cannot do a direct FORMATTING conversion between a number and a date because dates are stored as numbers with day 1 being 1-Jan-1900, day 2 being 2-Jan-1900 and each subsequent day increasing by 1. So you have to use a formula like this to construct the date piece by piece. =LEFT(A1, LEN(A1)-4) & "." & MID(A1, IF(LEN(A1)=5, 2, 3),2) & "." & RIGHT(A1, 2) This assumes that your 5 or 6 digit number is in cell A1 and your formula is in, say, B1. Results: 50621 converts to 5.06.21 (d.mm.yy) 150621 converts to 15.06.21 (dd.mm.yy) 61521 converts to 6.15.21 (m.dd.yy) 121521 converts to 12.15.21 (mm.dd.yy) Please note that the result is in text format rather than date format, so you couldn't use it in a date calculations etc. I hope this helps.
G'day buddy.. You have a couple of options. You can create a text string for your date using an IF / OR formula combination or you can do it using conditional formatting rules and retain the date serial number which means it can still be used in calculations. It's probably easier to point you to my blog post which shows the process step-by-step. There is also a free working example there that you can download and dissect. officemastery.com/excel-dates-like-1st-2nd-3rd/ All the best. Let me know how you go.
