Lotus 123, extract data from a date

Posted on 2003-03-10
Medium Priority
Last Modified: 2010-04-22
I have a column containing dates (if relevent of UK Format DD/MM/YYYY)I want to be able to extract components of the date to provide totals of the number of dates which were in the various quaters of the year (eg. Q1 = Jan, Feb, Mar) also I want to be able to provide totals of the number of dates within a specific year. A combination of the two. And finaly entries in the last three months (from todays date, but not a specific date, I want it to ajust according to what the date is.)

I need an answer as soon as possible hence the 500 points. Many Thanks.
Question by:darrellread
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
  • 3
LVL 13

Expert Comment

ID: 8102563
You'll find the month nr. using @Month(<date>).
Then you could use @If to convert those to quarters, or use @Quotient + correction to get the same result:
@Quotient(2 + <month no>; 3)

Still working on the totals....
LVL 45

Expert Comment

ID: 8104152
To find whether a date is in a particular quarter use a lookup table for simplicity. As

1     Q1
2     Q1
3     Q1
4     Q2
5     Q2
6     Q2
7     Q3
8     Q3
9     Q3
10     Q4
11     Q4
12     Q4

Say the name of this table is Quarters.
If in cell A1 you have the date 01/02/2003 (UK notation - dd/mm/yyyy)
Then use : @vlookup(@month(A1),$QUARTERS,1) will give Q1. You will need the $ in front of QUARTERS so that when you copy it down the column it always refers to that table. It works differently to Excel in that respect.

In the next column use: @YEAR(A1)+1900 and it will extract the year and correct it for the actual year by adding 1900 (from where Lotus123 starts to count dates).

At the botton of the QI column use: @COUNTIF(B1..B4,"Q1") where the range is only B1 to B4 for this example.

At the bottom of the year column use: @COUNTIF(C1..C4,2003)
where the range is only C1 to C4 for this example.

The last requirement I hope to figure out! It's illusive at the moment but I hope this helps to start with.

LVL 45

Accepted Solution

patrickab earned 2000 total points
ID: 8104451
The 'moving window' of the last 3 months is as simple as:
@NOW-90 where the 90 is just a number of days. The problem is to count all the dates which are greater than @NOW-90. So far I cannot get @countif() to do the job. I have assumed (perhaps incorrectly that today is the most recent day and that you are not logging dates in the future?)

An @if() statement will make valid datal comparisons but unfortunately Lotus123 cannot use range formulae (as far as I know!).

The way round this is to have a column with @IF(A1>$A$7,1,"") in it (copy it down the column) where A7 contains @NOW-90. At the bottom of the column use @count() as only those cells more recent than @NOW-90 will have a 1 in them. It should up-date automatically but if it doesn't then either F9 to get it to recalculate.

Hope this does it all for you.
LVL 45

Expert Comment

ID: 8104491
darrellread - please let me know how you get on with this one

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
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 …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

770 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