+ Reply to Thread
Results 1 to 3 of 3

Date Conversion Formula

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Date Conversion Formula

    Hi Guys,
    So I am using a formula to convert a date cell from: "mm/dd/yyyy" to "dd/mm/yyyy" with:

    =IF(ISTEXT(C7),DATE(RIGHT(C7,4),LEFT(C7,SEARCH("/",C7)-1),MID(C7,SEARCH("/",C7)+1,2)),DATEVALUE(TEXT(C7,"mm/dd/yyyy")))

    But I can't seem to figure out why it is turning date: 6/31/2017 into 01/07/2017, the result i want should be 31/6/2017.

    What do I need to tweak this??

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,103

    Re: Date Conversion Formula

    Perhaps because the date is invalid: there are only 30 days in June so June 31st=1 st July

  3. #3
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Date Conversion Formula

    Jeez, that's embarrassing. Thanks Topley

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date format conversion - help with formula
    By maillme in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2017, 02:43 PM
  2. Date Formula Conversion
    By johnsor1 in forum Excel General
    Replies: 1
    Last Post: 11-03-2015, 01:34 PM
  3. [SOLVED] Julian Date Conversion and Formula Modification
    By fearonc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2014, 02:33 PM
  4. [SOLVED] Need help with Date Conversion formula
    By rgs9467 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2013, 01:34 PM
  5. [SOLVED] =DATE conversion formula problem... and #VALUE question
    By RLSaSSy in forum Excel General
    Replies: 2
    Last Post: 01-25-2012, 10:59 AM
  6. Assistance with date conversion formula
    By maacmaac in forum Excel General
    Replies: 2
    Last Post: 01-15-2009, 01:27 PM
  7. [SOLVED] Need Formula for Date Conversion
    By Lyle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1