Assistance Required!!!

[TWIN HYPE]

Hi everyone,

I have a specific problem that I will try to explain as well as I can:

We have a new financial period every 4 weeks. We calculate information based on what period it is. If now is period 1 and you enter a payment it is registered as period 2. I have a 2 column list, column 1 shows periods 1 - 13 and column 2 the corresponding end dates. How do I get excel to look at today’s date and then round it up to the correct period end date as specified in column 2 i.e. if today is in period 1 (Nov 1 2005) and I enter a payment then show period 2 and its end date (Nov 12 2005).

Basic explanantion = if today() > period 1 date (B1) then round up to period 2 date (B2) and show in cell next to entry (D1) "Period 2" & Date.

It might not make that much sense but I'm feeling a bit rough this morning and this stuff is making my brain hurt!!!

Thanks in advance

Twin

 

[al_b_cnu]

Hi,

A bit hazy on a few details


If 12 Nov is end date of Period 2 and each period is 4 weeks, how is today in period 1?

in what cell do you enter the payment?

How do you want the results to show?

Do you mean something like this:

Book2
	A	B	C	D	E
1	Period	PE Date		Period	End Date
2	1	13-Nov-04	14	2	12-Nov-05
3	2	11-Dec-04
4	3	08-Jan-05
5	4	05-Feb-05
6	5	05-Mar-05
7	6	02-Apr-05
8	7	30-Apr-05
9	8	28-May-05
10	9	25-Jun-05
11	10	23-Jul-05
12	11	20-Aug-05
13	12	17-Sep-05
14	1	15-Oct-05
15	2	12-Nov-05
Sheet1

Formula in C2 =MATCH(TODAY(),B:B,1)
Formula in D2 =OFFSET($A$1,$C2,0)
Formula in E2 =OFFSET($A$1,$C2,1)

 

[TWIN HYPE]

Hey Al,

Yeah, sorry about the dates I kinda improvised on the spot and it didn't work out. The sheet looks about right but I have copied it over and I don't get any values in the cells. Lets say I enter a payment in cell C4 of £10,000 how do I get it to show the period and date in D4 and E4?

Thanks

Twin

 

[al_b_cnu]

Hi Twin,

So if you enter 10,000 in C4, will you be entering another value next month in C5, say, & expect D4 & E4 to remain unchanged?

 

[TWIN HYPE]

Hey Al,

Sorry about the confusing explanations but I am having one of those days where getting your head around what you want is difficult and explaining to someone else is even worse.
The periods and dates are just a reference for the calculation. Every time I enter a payment (column C) I would like the period and date to appear on the next 2 cells (D,E).

Thanks for your patience mate.

Twin

 

[al_b_cnu]

Hi Twin,

Dont worry about it - we ALL have those sort of days

What I was trying to ask / hint at was that if, say, in period 1 you entered 10,000 in C4 and the formula returned 2, 12 Nov 05.
You then come back to the s/sheet next month and enter 12,000 in C5, the formula in D4,E4 will re-calculate unless you either (a) convert the formula to text (could be operationally messy), (b) use a sheet change event, or (c) have the current date entered, say in C1, as a value for the formula to check Today() against.


Which way do you want to jump? (Do you want to go 50:50, phone a friend or ask the audience?)

 

[TWIN HYPE]

hmmmmm.....

We'll you appear to be both the audience and phone a friend so out of the 50 / 50 options..... I would like to recalculate the result as I will copy all the results out at the end of the period.

Could you do one of your cool sheets again so I can just do a brainless copy and I'll send you some lager in the post. (better get some type of waterproof envelope...)

Thanks Matey

Twin

 

[al_b_cnu]

Hi Twin,

OK, here it is:

Book1
	A	B	C	D	E
1	Period	PE Date		Period	PE Date
2	1	13-Nov-04	10,000	2	12-Nov-05
3	2	11-Dec-04	12,000	2	12-Nov-05
4	3	08-Jan-05
5	4	05-Feb-05
6	5	05-Mar-05
7	6	02-Apr-05
8	7	30-Apr-05
9	8	28-May-05
10	9	25-Jun-05
11	10	23-Jul-05
12	11	20-Aug-05
13	12	17-Sep-05
14	1	15-Oct-05
15	2	12-Nov-05
16	3	10-Dec-05
17	4	07-Jan-06
18	5	04-Feb-06
19	6	04-Mar-06
20	7	01-Apr-06
21	8	29-Apr-06
22	9	27-May-06
23	10	24-Jun-06
24	11	22-Jul-06
25	12	19-Aug-06
26	1	16-Sep-06
Sheet1

formula in D2 & copied down is =IF(ISBLANK($C2),"",OFFSET($A$1,MATCH(TODAY(),$B:$B,1),0))
Formula in E2 & copied down is =IF(ISBLANK($C2),"",OFFSET($A$1,MATCH(TODAY(),$B:$B,1),1))

Waterproof envelope on it's way

 

[TWIN HYPE]

Hi Mate,

I'm not getting any value returned!! am I doing something wrong? (probably)

Let me know

Twin

 

[al_b_cnu]

Hi Twin,

What's the layout of your sheet, how does it differ from my example?