Convert date to Text for filling CST / VAT form
When filling up CST / VAT form, you need date in text format. Here is the formula to convert the date into text format as required in CST / VAT form.
There are 3 ways you can achive this.
- Custom Format
- Formual
- User Defined Fucntion.
Custom Format
Go to format dialoque box, Go to Number Tab, and enter DD-MM-YY in custom type, as shown in image below.
Formula
|
1 |
=IFERROR(DAY(A1)"-"&REPT("0",2-LEN(MONTH(A1)))&MONTH(A1)&"-"&RIGHT(YEAR(A1),2),"Not a Date") |
In the above formula, I have assumed the date is in A1 Cell. If your date is in different cell, you can copy the formula to Notepad and replace the A1 with the cell reference where you have date.
Content expected in A1 : 31-03-2015 (Stored as DATE)
Formula generates : 31-03-15 (Stored as TEXT)
Formula assumes that the contents of the column is a DATE and not TEXT. If date is stored as a TEXT, then the above formula will give you answer as “Not a Date”. Moreover, formula works for both the date storage namely DD-MM-YYYY or MM-DD-YYYY
User Defined Fucntion
If you want to use this formula in many places, instead of using the big formula, you can just use simple to use UDF (User Defined Function – AdarshVATDate
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Function AdarshVATDate(dateString As String) As String '''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Created by : Adarsh Madecha ' Created on : 16 December 2015 ' Version : 1.0 ' Copyright : All rights reserved. ' Website: adarsh.madrecha.com ' Twitter : @adarshMadrecha ' If you want to share this code, ask that person to visit - adarshmadrecha.blogspot.com '-----------Purpose of this fucntion------------------ ' Background: For CST and VAT return filling (atleast in maharashtra) you need date in the format DD-MM-YY ' That too in text format. This fucntion will help you achive this. ' Using : Fuction accepts only one parameter. You need to provide only DATE in the fucntion, if any thing else is provide then yuou will get error as "Not a Date" ' : the fucntion will work if date is in the format MM DD YYYY or DD MM YYYY '''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Check if the data is date If IsDate(dateString) = False Then AdarshVATDate = "Not a Date" Exit Function End If 'Start string generation AdarshVATDate = Day(dateString) & "-" ' Date and - AdarshVATDate = AdarshVATDate & Application.WorksheetFunction.Rept("0", 2 - Len(Month(dateString))) ' 0 if month is one digit AdarshVATDate = AdarshVATDate & Month(dateString) & "-" ' Month and - AdarshVATDate = AdarshVATDate & Right(Year(dateString), 2) ' 2 digit year End Function |
Share the post with your Friends / Colleagues if you found it useful.
Don’t forget to subscribe, to receive new posts right to your inbox
Comment below, if you want any more functionality in this formula.
Thank you Mr Adarsh Madrecha. As you told us yesterday, you have posted formula for Date to Text. :). All the best
Can I just say what an aid to finding somebody who truly is aware of what they’re speaking about on the internet.