• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Lotus 123, extract data from a date

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.
  • 3
1 Solution
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....
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.

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.
darrellread - please let me know how you get on with this one

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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