=IFERROR(DAY(A1)&"-"&REPT("0",2-LEN(MONTH(A1)))&MONTH(A1)&"-"&RIGHT(YEAR(A1),2),"Not a Date")
Function AdarshVATDate(dateString As String) As String '''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Created by : Adarsh Madecha ' Created on : 16 December 2015 ' Version : 1.0 ' Copyright : All rights reserved. ' Website : madrecha.com ' Blog : adarshmadrecha.blogspot.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
Labels: Excel, Excel Tips