- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Text to Date Format NULL when converting
Hello,
this may be simple,
I have two date fields, one which is text (DateTermIssued) and one which is the renewal date.
I am trying to get a duration of the two dates, but I am unable to format the DateTermIssued field to be a date field?
I have tried many different date functions to try and convert it, but unable to do so?
Date(Floor(Date#( DateTermIssued1, 'MMM DD YYYY HH:MM:tt'),'DD/MM/YY')) as an example, just gives a null, can you see what I am doing wrong,
Jun 1 2021 12:00AM
This is how the date term issued field is showing the data in excel. MMM DD YYY HH:MM:tt
any help would be greatly appreciated,
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A bracket was misplaced, and it needs to be D and not DD.
Try
Date(Floor(Date#(DateTermIssued1, 'MMM D YYYY hh:mmTT')),'DD/MM/YY')
Date#(Trim(Left(DateTermIssued1,Index(DateTermIssued1,' ',3)-1)), 'MMM D YYYY')
Date#(Trim(Subfield(DateTermIssued1,':',1)), 'MMM D YYYY hh')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in the script:
Date(Floor(Date#( DateTermIssued1, 'MMM DD YYYY hh:mmTT'),'DD/MM/YY'))
Note that hours and minutes must be in lower case, and TT must be in upper case.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A second alternative is
Date#(Left(DateTermIssued1,Index(DateTermIssued1,' ',3)-1), 'MMM DD YYYY')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Henric,
Not working correctly, first formula NULL and the second formula gives me a result, but when i try to Format this into a date it nulls too,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A bracket was misplaced, and it needs to be D and not DD.
Try
Date(Floor(Date#(DateTermIssued1, 'MMM D YYYY hh:mmTT')),'DD/MM/YY')
Date#(Trim(Left(DateTermIssued1,Index(DateTermIssued1,' ',3)-1)), 'MMM D YYYY')
Date#(Trim(Subfield(DateTermIssued1,':',1)), 'MMM D YYYY hh')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Henric, The first solution worked.
Date(Floor(Date#(DateTermsIssued, 'MMM DD YYYY hh:mmTT')),'DD/MM/YYY') as DateTermIssued1,
Date(Floor(Date#(DateTermsIssued, 'MMM D YYYY hh:mmTT')),'DD/MM/YYY') as DateTermIssued2,
Because some of the day values have one and two numbers, ive had to use this IF to bring through the above dates
=IF(ISNULL(DateTermIssued1),DateTermIssued1,DateTermIssued1)
Thanks Again!!!