Excel Date Formatting

by

You can also use the TEXT() function quite easily (Note: source data must be an excel date value)

TEXT(value, format_text)

where value is the reference cell and format_text is how you want to format the text- in your case dd/mm/yyyy.

Assuming:

A1 = 3/17/2013
A2 = 12/27/2013

In B1 & B2 simply input:

B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")

and the result should be

     A              B
3/17/2013      17/03/2013
12/27/2013     27/12/2013

Hope that helps.

UPDATED SUGGESTION IF WORKING WITH TEXT:

Split the string using mid(), left() and right() functions then check to see if the month mm is 1 or 2 characters long using the LEN() function. Finally concatenatr the string together using the & and / operators.

Try pasting this in B1, it should work fine:

=MID(A1,FIND("https://stackoverflow.com/",A1,1)+1,2)&"https://stackoverflow.com/"&IF(LEN(LEFT(A1,FIND("https://stackoverflow.com/",A1)-1))=1,0&LEFT(A1,FIND("https://stackoverflow.com/",A1)-1),LEFT(A1,FIND("https://stackoverflow.com/",A1)-1))&"https://stackoverflow.com/"&RIGHT(A1,4)

More Related Contents:

  1. Autofilter with column formatted as date
  2. Changing the date format to yyyy-mm-dd
  3. Excel VBA: Convert a date string to a Unix timestamp
  4. Convert month name into number
  5. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  6. Excel Number Separation [closed]
  7. VBA Stuck after certain loop
  8. How do I use FileSystemObject in VBA?
  9. Is the . in .Range necessary when defined by .Cells?
  10. Add leading zeroes/0’s to existing Excel values to certain length
  11. Properly Handling Errors in VBA (Excel)
  12. Remove leading or trailing spaces in an entire column of data
  13. Password hash function for Excel VBA
  14. Split a string (cell) in Excel without VBA (e.g. for array formula)
  15. Read Excel sheet in Powershell
  16. Aggregate, Collate and Transpose rows into columns
  17. VBA Excel sort range by specific column
  18. How to get images to appear in Excel given image url
  19. Select method of Range class failed via VBA
  20. Delete cells in an Excel column when rows = 0
  21. Comparing two columns, and returning a specific adjacent cell in Excel
  22. How to add events to dynamically created controls (buttons, listboxes) in Excel VBA userform
  23. How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?
  24. Excel: Dropdown list dependant on other dropdown list
  25. how to transform a table in Excel from vertical to horizontal but with different length
  26. How do I reference tables in Excel using VBA?
  27. Handle cancellation of InputBox to select range
  28. VBA: How to combine two ranges on different sheets into one, to loop through
  29. How to protect Excel workbook using VBA?
  30. Unique Rank value for a subgroup within a group

Leave a Comment