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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Querying received e-mails from an agent 7 204
Lotus notes send email with attachments 8 162
Lotus Notes 6.5.5 6 208
IBM Lotus notes 9 89
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses 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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

864 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now