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
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)
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.
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.
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
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
👍
Thanks for the video .very usefull date and formating
Thank you Abhi.
Thanks very much, very helpful
@WorldOfwilde No worries. Glad to help.
Perfect, just what I was looking for. Thanks Muchly :0)
No worries Zerandervax. Glad to help.
really useful. Is there a way to produce the date as say the 5th, 3rd, 28th, 2nd and so forth
Yes, check out my blog post here:
officemastery.com/excel-dates-like-1st-2nd-3rd/
Thanks Sir,
Most welcome
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)
Nice! But how do you add the "th" or the "rd" to those custom dates?
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.
Thank you for this. But my problem is how to conver 5 digits to date. D.mm.yy or m.dd.yy. please help
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.
@@JasonMorrell very helpful.🙏🙏I appreciate. Thank you
how can you get the input mm/dd/yy or 7/11/22 to show as July 11, 22?
Use the custom format mmmm, dd, yy
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