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.
I need an answer as soon as possible hence the 500 points. Many Thanks.
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),$QUART
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.
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),$QUART
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
darrellread - please let me know how you get on with this one
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....