3 days in then. Two more and you will be a complete expert as it all comes together!
Dates and times are very strange things. I tried your formula and got a completely different result. (Monarch Pro Version 7.01 )
Firstly converting the entire string gave 10/09/2327 - which is much as I would expect though I was impressed that the "-" was interpreted at all. 10/09 alone was not understood presumably because it lacks a year number.
Secondly your final output format, with the day of the week at the beginning of the field, is a facet of the regional settings in windows. This means that anyone else using the model, if they have different settings on their PC, could see a different display format. Whether this is a problem is really for you to decide. Probably it is not for your purpose. However I don't think Monarch stores that sort of information in the model, only the expected date format for the input report and the filed level output formats overlaying the Windows system format setting.
I could find nothing to give the Day description consistently, independently of system settings, other than using the day-of-week number feature - Weekday(,)- and a look up table. I mention this purely for completeness for anyone developing models for sharing amongst a team who may have different preferences set on their PCs.
So, in respect of the year identification is there more to the formula(s) you are creating than you posted? I am always eager to find any new ways of doing things.
Other than that your formula basically is good although the lack of a year number in the date string would be a concern for me if I was working with the source information. I suppose you have to make some assumptions about current year processing.
The following is a simple version of a solution which works through intermediate fields for clarity. To do it in one field just replace the fields in the final formula with the formulas that create them. I have left them as individual fields only because it is easier to follow the formula (and also easier to develop and check the model!). Once the result is what you want simply generate the 'master' formula and hide or delete the 'work' fields.
To extract the "date" field (as character for now) and add a year value:
This will give 10/09/04.
To extract the "Hours" field and format it with a ":", also as a character field;
This will give 23:27 . ( I believe the ":", or whatever the established hours to minutes separator may be defined as, is significant here to identify time rather than a year.
Put them together and convert to a date.
ctod((Date" "Hours), "m/d/y", "DT")
On my system, set to UK style system displays, this gives;
09 October 2004 23:27:00
You should get your
Saturday, October 09, 2004 23:27:00
if the field is set to Long Date & Time format.
I guess you could also modify the format to drop the seconds if required.
To the best of my knowledge if you want to consistently add the Day by name at the beginning of the field, irrespective of system settings, you would need to get the weekday numeric value and look that up in a fixed lookup table and then add the concatentation to the beginning of this formula, with a "," if required.
I would be happy to learn of another way though and I know there are quite a number of different approaches that could be followed to arrive at this result.
I hope this helps.
What I have is a report that represents date and time as a string such as 10/09-2357. The month and day are followed by a "-" and then the time. The format type is character, and I need to convert it to date/time format so I can calculate duration between different times (order entry, collection time, received time, result entry time, and verified time). Each of these times is formated the same way. The duration needs to account for the date since some tests can take longer than a day to complete, and the time of verification may be a day after the time of collection. I would also like to have the day of the week shown (long form of the date).
This is what I've done so far:
This returns the following:
Saturday, October 09, 2004 00:00:00
The time (2327) is wiped out. I've tried using "DT" at the end of the calculation, and that doesn't help. I get "(Null)."
Thanks in advance for any help anyone can offer. /b[/quote]
I'v e no doubt there is a quicker method or you could probably combine some of these calculated fields, but this works.
First in the report, extract the date and time as 2 fields. First is date1, character field with the 10/9 highlighted, second is Time, date and time formatted as Time with the 2327 highlighted. Do not extract the "-" as you do not need it.
Next set up the following calculated fields in the table.
date2 = date1+"/2004" as character. I have used the "/2004" for simplicity but you can experiment with other fields to get the year.
Date and Time = ctod(date2,"m/y/d","D")+time
Any problems, send me a private message and I will email the model file.
Nigel has spurred me to look at an alternative which seems easier for V7 users.
To get DATE. Format - Date.
To get TIME. Format - TIME
To get date and time, simply add the two fields together.
Format - Long Date and Time
In this case the one step formula for field substitution seems not to work as the concatentation above relies upon Monarch understanding the TIME field as time. The two field and then add them together approach seems to be the easier way here.
First, thank you helping me solve this problem. I've now got another problem to tackle that is related to the first problem, though.
Here's what I did that finally worked:
I am concerned that the "/04" to add the year means this will have to be changed each year. I think I can insert a calculation to borrow the date from another field in the record to solve that problem.
Now, here's the next issue that is stumping me. I need to calculate duration between several time points. I thought this was simple. Here's what I've done:
Type - Numeric
Format - Time span
The calculation yields: 00:00:00
I know timespan assumes seconds so that's why I tried converting the calculated date/time field to seconds before calculating the duration. I'm missing something, though, as 00:00:00 isn't correct. Here's an example of what I'd like the calculation to yield:
Seconds(collected date/time) - Seconds(ordered for date/time) = duration in hh:mm:ss
10/9/2004 23:55:00 - 10/10/2004 00:03:00 = -00:08:00 (collected 8 minutes before order entry)
Here are all the calculated field names in my table and the durations I'm trying to calculate:
CALCULATED FIELD |EXAMPLE OF VALUE
ORD ordered for date/time |10/10/2004 00:03:00
COL collected date/time |10/9/2004 23:55:00
REC received date/time |10/10/2004 00:12:00
ENT entry/insert date/time |10/10/2004 00:26:00
VER verified date/time |10/10/2004 00:30:00
NEED CALCULATIONS FOR THESE DURATIONS:
Thanks for any help that's offered. This forum seems to be the best resource I've found outside of the pdf files from datawatch.com.
Glad that worked though I am a little puzzled that the formula worked for you but didn't work for me!
Must check that again ...
Your year problem worries me a little as it is so obviously a potential problem. Especially on change of year.
You could slice the DATE() value I guess and use that rather than "/04" PROVIDING you can always assume that current year is OK.
seems to work fine but looks a little clumsy. Probably a few other ways to get there if you want to seek them out.
(A word of warning if using TIME() as well as DATE(). THe TIME() value displayed always appears to be the time at which the Monarch session started. If you are running the process as an automated batch file that shoukd be fine but if you are using a session that has been logged in for a while you may not get the most accurate current time returned.)
As for the other question I think you should be able to simply deduct one D+T value from the other. However this seems to give an answer in decimal days. In other words if the calculation field is numeric or even time span you will get something like 2.5 (days) as the result. Or .003 minutes or something equally small. Unless you convert to seconds first as you suggested.
Alternatively you can do the calculation and multiply the result by 86400 (the number of seconds in a day) and then set the format to Numeric and "time span" and you should see the values time difference you require although it will be displayed in HH:MM:SS.SS format rather than showing days than hours.
I think you have 2 problems in your earlier formulas.
Firstly the SECOND function is not the correct one to use I suspect. Have a look at the ChartoSeconds and SecondstoChar functions as descibed in the Help file. However using that route means converting the date to a character value then to seconds before deducting one time from the other and displaying the result as a time span.
Subtracting one Date and Time field from another and then multiplying the result by 86400 seems a little easier to me. However it is a matter of personal preference.
I hope this helps. Errors, omissions and typos excepted!
[size="1"][ October 13, 2004, 06:25 AM: Message edited by: Grant Perkins ][/size]
I successfully tried the following:
I think I was confused by the 00:00:00. This appears as if the calculation wasn't working. If I expanded the number of decimals, though, it was clear that the calculation was returning something. I might have then clued into the need to multiply the result by 86400.
Thanks for your help, again.