How can I formt a date in Excel in the form mm/dd that switches t.

G

Guest

I have an application that uses the mm/dd format. However, some of our users
are in the UK and I want to display the date as dd/mm, i.e. it switches
automatically depending on the region. Excel does have a format that switches
depending on the region (it's prefixed with a *) but it's in a form
mm/dd/yyyy and I only want mm/dd.

Anyone know if this can be done?
 
J

Juan Pablo González

I think you need VBA for that. This UDF *should* do it

Function ShowDate(dDate As Date) As String
ShowDate = Format$(dDate, "short date")
Select Case Application.International(xlDateOrder)
Case 0, 1
ShowDate = Application.Substitute(ShowDate, _
Application.International(xlDateSeparator) & Year(Date), _
"")
Case 2
ShowDate = Application.Substitute(ShowDate, _
Year(Date) & Application.International(xlDateSeparator), _
"")
End Select
End Function


Use it like

=ShowDate(A2)

where A2 houses your date.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? 2
Excel 2002: How to make the date format uniform ? 1
want formula bar date to be dd/mm/yyyy instead of mm/dd/yyyy.How? 1
convert Julian date to DD/MM/YYYY 7
Sumproduct and weekday from mm/dd/yyyy format? 3
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y 2
Changing date to eliminate day in format mm/dd/yyyy 1
Forcing a date entry format of dd/mm/yyyy 1

Top