Assuming you have version 8 Monarch AND that your Entry Time field properties make it a Date/Time field Type with Time format, the following formula would be one way to do what you want.
Your calculated field will need to be defined as a Character type and General format for this forumal to work. I have assumed, to keep things simple, that a Character display will be all you need. If that is not correct or you are using a version of Monarch without the required functions for the formula above, let us know and we can seek other options.
I am actually using Monarch Version 5.00. /b[/quote]Melissa,
Hmm, the previous formula definitely won't work as it uses functions and features introduced after V5.
My PC with V5 installed died recently and I have not yet got it running again. However I don't think anything required for this problem changed much between V5 and V6 so here is a V6 solution which should work.
As far as I can remember the TIME string value will be a CHARACTER field in a V5 interpretation, which makes life easier in many ways. This formula will give you a calculated character field displaying the hours with minutes and seconds set to zero but looking like a full time display. The original field being converted is named
This tells Monarch to do the following:
This gives the Hour value.
Add on (the + used to CONCATENATE characters) the string of characters specified within the quotes (:00:00)
Display the result in a character defined field and you should have what you need.
This will not work unless the original field is character based (not numeric, date, etc.). However the other field types can usually be converted to a character field if necessary, using other Monarch functions, as part of the formula.
I hope this helps.
Edit for Typos ...
That worked perfect! Thank you very much. One more thing though... now the person I am doing this work for wants it in half hour increments. How do I go about that?? For example:
7:31:00 | 7:30:00
8:57:00 | 8:30:00
9:21:00 | 9:00:00
So, anything between :00 and :29 would be referred to :00. and any time between :30 and :59 would be referred to as :30. Get it? Is there a way I can do that?
Thanks!!! ~ Melissa ~
Yup, you can do that with this formula:
":00"[/font][/quote]The only reason I have split it like that is to show it in 3 sections. If you use it you can join the sections together removing the spaces.
The first line is the hours processing which is the same as the previous formula.
The second sections - (which ideally should display all on one line once the message entry window is finished with) checks the value of the hours part of the string and defines whether to display 00 or 30.
The third section is the same as the first formula. (If you prefer to make the formula easier to follow you coud create some intermediate calculated files - One for HOUR (the first section) and one for MINUTE (The formula in the second section. You couold then build them into a third calculated field which would have a formula something like
To explain the MINUTE formula.
I use the LSPLIT function again and this time split the string into 3 sections so we can get a value for the minutes in part 2 only.
[font="courier"]lsplit(,3,":",2)[/font][/quote]Now we need to compare this to 30 to decide if the the value to display should be 00 or 30. To do that we need to convert the character string to a numeric value using the VAL() function.
Putting this into an IF() function allows us to decide what to to do with the result of the comparison.
IF the numeric value of the Minutes part of the time string is less than 30, make the value to be used (in CHARACTERS not numeric) ":00", ELSE make it ":30".
If your co-worker changes their mind and wants 15 minute resolutions instead, you can do that in much the same way but you will need to include 2 extra checks of the minute value by 'Nesting' IF() functions within the formula and checking the number of minutes against a RANGE of values. A little more complex but not too bad once you get the range check right. However, lets keep it simpler for now.