Lotus formula Question

Posted on 2006-11-10
Last Modified: 2013-12-18

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.

Question by:davidw88
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Author Comment

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.

LVL 19

Expert Comment

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.
LVL 18

Accepted Solution

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:

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") +

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.

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:=@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") +

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);
LVL 18

Expert Comment

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

Author Comment

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.

Featured Post

Industry Leaders: 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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

628 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