Improve company productivity with a Business Account.Sign Up

x
?
Solved

Lotus formula Question

Posted on 2006-11-10
6
Medium Priority
?
788 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
5 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 600 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

579 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