Date strategies in calculations

[vbthedog]

I am trying to work out a system of "Actual" versus "Budget" based on today's date. I have a column per month of expenses and at the bottom, it is totalled. Below that is a budget for that month for the entire column. As each day of the month progresses, I want the next cell down to show the % of total over or under budget for that month used to date.

So I need the cell to work on what number day of the current month it is to perform the calculation for this month, but then, once the month has finished (or even not yet started) stop calculating.

Make sense?

TIA

David

 

[Toadstool]

Hi vbthedog,

For my example I've used cell B1 as the date for test purposes. You can replace with =TODAY()

You said you only want to calculate for the current month so others are made null.

For the headings I've made each month the first of that month but displayed just the month name with format mmmm

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	As at	9/15/2020
2
3		1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020
4	Allstate-Car	42	42	42	42	42	42	42	42	42	0	0	0
5	Allstate-House	22	22	22	22	22	22	22	22	22	0	0	0
6	AT&T	39	39	39	39	39	39	39	39	39	0	0	0
7	Broadband	76	76	76	76	76	76	76	76	76	0	0	0
8	Gas	42	42	42	42	42	42	42	42	42	0	0	0
9	Water/Power	87	87	87	87	87	87	87	87	87	0	0	0
10	Mortgage	812	812	812	812	812	812	812	812	812	0	0	0
11	Other	80	80	80	80	240	80	80	80	80	0	0	0
12
13	Total	1200	1200	1200	1200	1360	1200	1200	1200	1200	0	0	0
14	Budget	900	1000	1000	1000	1000	1000	450	1000	1000
15	% over/under									140.00%
Sheet1 (2)

Cell Formulas
Range	Formula
C3:M3	C3	=EOMONTH(B3,0)+1
B13:M13	B13	=SUM(B4:B11)
B15:M15	B15	=IF(OR($B$1<B$3,$B$1>EOMONTH(B$3,0)),"",(B13-(DAY($B$1)/(EOMONTH(B3,0)-B3+1))*B14)/((DAY($B$1)/((EOMONTH(B3,0)-B3)+1))*B14))

 

[vbthedog]

Blimey I knew it might be tricky so thank you! The only difference is that after a month has completed,, I want the final +/- budget figure to stay fixed as a fixed value as it was at the month end.

 

[pedro-egoli]

I only have Excel 2003 and cannot copy my test spreadsheet but this might give you some ideas from my test

Step 1. Enter Last day of previous month in cell H2 Last day current month I2

Step 2 In J2 get number of days for month “=SUM(I2-H2)”

Step 3 In K2 get daily budget figure “=Budget/J2)

Step 4 In F3:J3 enter these headings “Date,Days completed,Budget,Actual,%Difference”

Step 5 Enter Date in F4

Step 6 Formulae as follows

G4 =$J$2-SUM($I$2-F4) “DAYS”

H4 =$K$2*G4 “BUDGET”

I4 Expenditure to date from your spreadsheet

J4 =SUM(H4-I4)/H4 “% DIFF”

Step 6 Insert date and drag down formulae for each subsequent day of month
A screenshot

 

[Toadstool]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	As at	9/15/2020
2
3		1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020
4	Allstate-Car	42	42	42	42	42	42	42	42	42	0	0	0
5	Allstate-House	22	22	22	22	22	22	22	22	22	0	0	0
6	AT&T	39	39	39	39	39	39	39	39	39	0	0	0
7	Broadband	76	76	76	76	76	76	76	76	76	0	0	0
8	Gas	42	42	42	42	42	42	42	42	42	0	0	0
9	Water/Power	87	87	87	87	87	87	87	87	87	0	0	0
10	Mortgage	812	812	812	812	812	812	812	812	812	0	0	0
11	Other	80	80	80	80	240	80	80	80	80	0	0	0
12
13	Total	1200	1200	1200	1200	1360	1200	1200	1200	1200	0	0	0
14	Budget	900	600	1800	1000	1000	1200	800	1000	600
15	% over/under	33%	100%	-33%	20%	36%	0%	50%	20%	300%
Sheet1 (3)

Cell Formulas
Range	Formula
C3:M3	C3	=EOMONTH(B3,0)+1
B13:M13	B13	=SUM(B4:B11)
B15:M15	B15	=IF(EOMONTH(B$3,0)<$B$1,(B13-B14)/B14,IFERROR((B13-(DAY($B$1)/(EOMONTH(B3,0)-B3+1))*B14)/((DAY($B$1)/((EOMONTH(B3,0)-B3)+1))*B14),""))

 

[vbthedog]

Thank you one and all. That would have taken me a month of Sundays to work out! Genius, sheer genius