[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

Lotus formula Question

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
davidw88
Asked:
davidw88
  • 2
  • 2
1 Solution
 
davidw88Author Commented:
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
 
madheeswarCommented:
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
 
marilyngCommented:
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
 
marilyngCommented:
oops, the last formula translates today's date.
0
 
davidw88Author Commented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now