0

How can I copy from a text field to a date field?

The text looks like dates but doesn’t sort properly.

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Sean
    • Sean
    • 3 yrs ago
    • Reported - view

    You can use "Update multiple records..." The formula below assumes the text date is in the format of Day/Month/Year. Make sure you have created a Date field to copy the value to. When you select "Update multiple records...", find your new Date field and click where it says "Don't change". Choose "Assign calculated value" and enter the formula...

     

    date(number(substr(YourTextDate, 6, 4)), number(substr(YourTextDate, 3, 2)), number(substr(YourTextDate, 0, 2)))

     

    Choose Next and it will give you a preview.

    Like
  • No, that doesn’t work. My dates are in m/d/yyyy format with leading zeros omitted from the month and day. 

    Like
    • Sean
    • Sean
    • 3 yrs ago
    • Reported - view

    It’s still possible, just take a little more code. It will be a few hours before I can work on it again.

    Like
    • Sean
    • Sean
    • 3 yrs ago
    • Reported - view

    This should do it...

     

    let firstIndex := index(TextDate, "/");
    let secondIndex := if substr(TextDate, firstIndex + 2, 1) = "/" then
    firstIndex + 2
    else
    firstIndex + 3
    end;
    date(number(substr(TextDate, secondIndex + 1, 4)), number(substr(TextDate, 0, firstIndex)), number(substr(TextDate, firstIndex + 1, secondIndex - firstIndex - 1)))

    Like
  • It does. Thanks!

    Like
    • Mconneen
    • Mconneen
    • 3 yrs ago
    • Reported - view

    @bonniegruber.... You can also use the "split" or "splitx" function that will split the string into an array.. the "x" method uses a regular expression .. .which you can craft to handle multiple date separators .. Like "/" or "-" ... 

    Like
    • Dr HH
    • Dr_HH
    • 2 yrs ago
    • Reported - view

    Can this  formula  be  modified for > The  TextDate field (yyyy/mm/dd) to be updated to all fields to a NEWDAte field  in the format  dd/mm/ yyyyy

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    The Date field will be in the format that is set in your OS if you are using the Mac app or your browser settings if you are using the browser version.

     

    @Mconneen's formula in this thread https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/convert-5e88c4836280830edf1ce983?post=5e899a686280830edf1ce991&page=1 works, but assumes the Text date is in the same order that you want the Date field to be in.

     

    What is the format of your TextDate? yyyy/mm/dd, dd/mm/yyyy or mm/dd/yyyy?

    Like
    • Dr HH
    • Dr_HH
    • 2 yrs ago
    • Reported - view

    yyyy/mm/dd  that is  how  it  is  in the  TExtfield in my database 

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    You just need to change the order of the formula then. The date() function takes 3 arguments in this order... Year, Month, Day. You can use the following for the "Assign calculated value" formula of the new Date field when you use "Update Multiple Records..."

     

    let dateArr := split(TextDate, "/");
    date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2)))

    Like
Like Follow
  • 2 yrs agoLast active
  • 10Replies
  • 2793Views