Solved

Lotus formula Question

Posted on 2006-11-10
6
735 Views
Last Modified: 2013-12-18
Hello,

Here is a Lotus formula question.

A piece of message contains date/time of this format like "10-Aug-2005 01:04 PM". This mesage also contains a date/time format like "07/15/05" and "3/22/04". My task is to convert all the date/time format above into a format of "YYYYMMDDHHMMPM". (The suffix may also be "AM" depending on the original date/time in the message.) For example, the "10-Aug-2005 01:04 PM" will be converted into "200508100104PM";  "07/15/05" will be "20050715" and "3/22/04" will be "20040322".

The date/time may occur any place in the message.

I am using a configuration form to process this message on Lotus Domino Designer/Notes (v6.5). On this form there are "Field Variable" and "Formula to Process Mesage". The processed message will be assigned to the "Field Variable" automatically; therefore there should be no assignment ("=") in the formula that you give.

In order to show my appreciation of your answers, I assign this question credit to 150. If more than one answer is valuable, then I will split the credit accordingly.

Many thanks.

0
Comment
Question by:davidw88
  • 2
  • 2
6 Comments
 

Author Comment

by:davidw88
ID: 17919619
My Super just told me that he preferred to leave the original date/time format untouched. Though this is the case, I am still interested in learning how it can be done with formula. I will still give you the credit as long as you can provide me an answer.

0
 
LVL 19

Expert Comment

by:madheeswar
ID: 17927530
I assume you want to fix the existing time date format in the documents. If it is the case, then using Lotus Script you can fix the data.

use Format funtion in lotus script which does this. like:
tmpdate=Format("yourdatefiled","ddmmyyyy ampm")

check in designer help for more info.
0
 
LVL 18

Accepted Solution

by:
marilyng earned 150 total points
ID: 17930359
Hmm.  >>The date/time may occur any place in the message<<   This suggests to me that you have a memo of sorts which contains a date, time.  Is this a Form?  or an e-mail that you are pushing to a form?

If this is a form with fields and there are many date/time fields where the date entered is plain text, then you can use a script action or agent , or translation formulas to convert the fields to the date format.

If this is a message, then you have to search through the message to find "dates" and convert them.

Script is easier as Maddy suggests, because of the format function.

In formula it's a bit more involved.  But both need to know if it's a date or needs to be converted to a date.

A field Translation or computed value IN FORMULA LANGUAGE that can be added to your form:

thisDate:=@TextToTime(@Text(YOURDATEFIELD;"S2T1"));
thisTime:=@if(@Hour(thisDate)>0 & @Minute(thisDate)>0;
    @ReplaceSubstring(@Text(@TextToTime(thisDateT);"S1T1");" ";"");"");
@If(YOURDATEFIELD="" |(@Year(thisDate)<0 & @Month(thisDate)<0  & @Day(thisDate)<0);"";
@If(@Year(thisDate)>0;@Text(@Year(thisDate));"00") +
@if(@Month(thisDate)>0;@Right("00" + @Text(@Month(thisDate));2);"00") +
@If(@Day(thisDate)>0;@Right("00" + @Text(@Day(thisDate));2);"00") +
thisTime)

----------------------
Similarly you can add an action button that will update the individual fields.  Suppose you have field1 and field2 that you need to convert. Then you would paste this into the action button and make the button available when the form is in EDIT mode.

thisDate:=@TextToTime(@Text(FIELD1;"S2T1"));
thisTime:=@if(@Hour(thisDate)>0 & @Minute(thisDate)>0;
FIELD1:= @ReplaceSubstring(@Text(@TextToTime(thisDateT);"S1T1");" ";"");"");
@If(FIELD1="" |(@Year(thisDate)<0 & @Month(thisDate)<0  & @Day(thisDate)<0);"";
@If(@Year(thisDate)>0;@Text(@Year(thisDate));"00") +
@if(@Month(thisDate)>0;@Right("00" + @Text(@Month(thisDate));2);"00") +
@If(@Day(thisDate)>0;@Right("00" + @Text(@Day(thisDate));2);"00") +
thisTime)

thisDate:=@TextToTime(@Text(FIELD2;"S2T1"));
thisTime:=@if(@Hour(thisDate)>0 & @Minute(thisDate)>0;
FIELD2:= @ReplaceSubstring(@Text(@TextToTime(thisDateT);"S1T1");" ";"");"");
@If(FIELD2="" |(@Year(thisDate)<0 & @Month(thisDate)<0  & @Day(thisDate)<0);"";
@If(@Year(thisDate)>0;@Text(@Year(thisDate));"00") +
@if(@Month(thisDate)>0;@Right("00" + @Text(@Month(thisDate));2);"00") +
@If(@Day(thisDate)>0;@Right("00" + @Text(@Day(thisDate));2);"00") +
thisTime)



This Formula converts to yyyymmddhhmmss( Can be added to a translation formula)

Num:=@Text(@Year(@Now)) + @Right("00" + @Text(@Month(@Now));2) + @Right("00" + @Text(@Day(@Now));2) + @Right("00" + @Text(@Hour(@Now));2) + @right("00" + @Text(@Minute(@Now));2) + @Right("00" + @Text(@Second(@Now));2);
num
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17930377
oops, the last formula translates today's date.
0
 

Author Comment

by:davidw88
ID: 17934598
Hi Marilyng,

Thanks so much for your answer. Here is a little more background information. The message that I was intending to process was saved in a database already. I used a configuration file to retrieve the message body.

I have read your answer carefully.  Though I did not test it, I think it should work as it is correct logically.

And thanks to Madheeswar.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question