Assistance Required!!!

TWIN HYPE

Board Regular
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!!!

Twin

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
ABCDE
1PeriodPE DatePeriodEnd Date
2113-Nov-0414212-Nov-05
3211-Dec-04
4308-Jan-05
5405-Feb-05
6505-Mar-05
7602-Apr-05
8730-Apr-05
9828-May-05
10925-Jun-05
111023-Jul-05
121120-Aug-05
131217-Sep-05
14115-Oct-05
15212-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)

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

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?

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).

Twin

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

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

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

Hi Mate,

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

Let me know

Twin

Hi Twin,

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

Replies
2
Views
160
Replies
0
Views
201
Replies
1
Views
159
Replies
2
Views
205
Replies
1
Views
1K

1,207,259
Messages
6,077,349
Members
446,279
Latest member
hoangquan2310

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 MrExcel.com.

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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