Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Getting date in below format :
Explanation of date field format :- 1/1/4000 = 0 days
(1st octet is month, 2nd Octet is days and 3rd Octet is hours).
For eg: 000 8:22:13 (7 months, 21 days, 13 hrs)
This is required to be converted to standard format in minutes i.e(mm)
Interval(makedate( subfield('000 8:22:13', ' ', 1) + 4000, subfield(subfield('000 8:22:13', ' ', 2), ':', 1)-1, subfield(subfield('000 8:22:13', ' ', 2), ':', 2)-1) + num#(subfield(subfield('000 8:22:13', ' ', 2), ':', 3))/1440 , 'mm')
Thanks Gysbert,but it does not solve the problem.
When i am replacing date field with the hard-coded value in your expression,it gives me null.
Sample Data:
TOTAL_TIME |
1/1/4000 6:32 |
1/1/4000 0:00 |
1/1/4000 12:12 |
1/1/4000 7:35 |
Your sample data is completely different from the format 000 8:22:13 that you posted. Please give the correct definition of the format.
The below clarification was been provided to me on asking the date format.
Explanation of date field format :- 1/1/4000 = 0 days
(1st octet is month, 2nd Octet is days and 3rd Octet is hours).
For eg: 000 8:22:13 (7 months, 21 days, 13 hrs)
Not Sure what is the difference here.
Even to me data looks very different with no value matching the example.
I will provide the required details after confirming with data provider
1/1/4000 is not the same format as 000. So what date and time is 1//1/4000 6:32? And what should the end result be for that value?
Hi Gysbert,
I got some clarity with the data.
From 1/1/4000 ,1 and 4000 has to be directly removed leaving middle section which gives days left ,which is 1 less than mentioned days.
Example:1/n/4000,
Processed data=n
lets have 1/3/4000 3:45:12 AM
It means 2 days 3 hours 45 mins 12 sec.
For getting this part ,i have used below expression:
=Timestamp#( IF(WildMatch(TOTAL_TIME,'*1/1/4000*'),date(TOTAL_TIME,'hh:mm:ss'),DATE(date(TOTAL_TIME,'d hh:mm:ss')-1,'d hh:mm:ss')) ) ,which is getting me correct data.
Now i want to add this field value to another field value.
Ex: a field named start_time has to be added with TOTAL_TIME FIELD.
Let's say start_time for a ticket is '7/13/2016 3:45:12 TT' and Total_Time field for the same is :2 days 3 hours 45 mins 12 sec.
So how can we add these 2 fields. (i.e. 7/13/2016 3:45:12 PM + 2 3:45:12) wherein 2 days 3 hours 45 min and 12 sec has been added to open_time field.
How to achieve this addition ?
Add them: field1 + field2. Just make sure that one of the fields is a date or timestamp and the other is an interval of days+time.