Assistance Required!!!


Board Regular
Aug 25, 2005
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


Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

A bit hazy on a few details :confused:

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:
1PeriodPE DatePeriodEnd Date

Formula in C2 =MATCH(TODAY(),B:B,1)
Formula in D2 =OFFSET($A$1,$C2,0)
Formula in E2 =OFFSET($A$1,$C2,1)
Upvote 0
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?


Upvote 0
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?
Upvote 0
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. :biggrin:
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.

Upvote 0
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?)
Upvote 0

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

Upvote 0
Hi Twin,

OK, here it is:
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 :biggrin:
Upvote 0
Hi Mate,

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

Let me know

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back