Perpetual balance update

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Greetings!
I have a yearly calendar that I keep track of my time off. I have it set up to where when I put a "T" above a date, eight hours is automatically subtracted from my available balance of time off (or "T4 and four hours are subtracted). The issue is my available amount of time off increases every 14 days and the balance that the company shows that I have will not match what my spreadsheet has because I reserve time off throughout the whole year. What formula can I put into a cell that will give me this answer: Beginning balance - balance used + balance earned every 14 days. For example:
Beginning balance: 300 hours (final balance taken from the previous year's calendar and manually entered)
Balance used (5 days in one month, 3 days in another, 1 day in another): 72
Balance earned every 14 days: 15 hours (this can be different based upon the number of years you've been with the company, so I would reference a cell in the formula)

I tried using the formula given in the "Add 2.57 Hours Every Sunday" thread, but it's not working quite right.

I have the rest of the worksheet set up where one cell reads the available number of days (E6), another cell shows the total number of days taken (E7) based upon what has been entered onto the calendar, and a third cell (I6) that shows the number of days available by subtracting the number from E7. Cell E6 reads the number of hours available in cell R68 and divides it by eight. So it looks like the above-requested formula's results needs to show up in R68. My calendar covers cells C8 to Y66 with each month being seven columns x 14 rows (first row is the name of the month, next row is the days of the week, and then every other row after that is the date with the cell above it reserved for the time-off indicator (T or T4)). It is a perpetual calendar in that I just need to put the year in cell U3 and the dates are automagically updated. I wish I could say that I was the creator of this, but I'm not! I just tweaked it for my use.

If you need more information, please feel free to ask!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Additional information - if you need/want to see how my calendar is laid out, refer to this thread: "Can I change the color of one cell based upon the color of another cell using conditional formatting?"
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
may you post worksheet to be clear mor
 

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Here's the calendar. It's strange that the columns aren't the same width. They are on my spreadsheet. One more thing - the initial placement of the dates are controlled by entering a year, and that then somehow triggers the dates being entered on the worksheet. I didn't copy the other worksheet that the year refers to because I don't think it is relevant to the request.
Yearly Calendar_TimeOff_master.xlsx
BCDEFGHIJKLMNOPQRSTUVWXY
6PTO days:0.0Days Left:0.0Sick Time0Days Left:0.0
7Taken0.0DaysTaken0Hours0days
8JANUARYFEBRUARYMARCH
9SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
10
11     12 123456  12345
12
133456789789101112136789101112
14
15101112131415161415161718192013141516171819
16
17171819202122232122232425262720212223242526
18
19242526272829302829     2728293031  
20
2131             
22
23APRILMAYJUNE
24SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
25
26     121234567   1234
27
283456789891011121314567891011
29
30101112131415161516171819202112131415161718
31
32171819202122232223242526272819202122232425
33
3424252627282930293031    2627282930  
35
36          
37
38JULYAUGUSTSEPTEMBER
39SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
40
41     12 123456    123
42
4334567897891011121345678910
44
45101112131415161415161718192011121314151617
46
47171819202122232122232425262718192021222324
48
492425262728293028293031   252627282930 
50
5131    
52
53OCTOBERNOVEMBERDECEMBER
54SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
55
56      1  12345    123
57
582345678678910111245678910
59
6091011121314151314151617181911121314151617
61
62161718192021222021222324252618192021222324
63
642324252627282927282930   25262728293031
65
663031   
67KeyInitial # of days available
68PaydayHolidayETO balance0.0Sick:
Time-off_tracking
Cell Formulas
RangeFormula
I6I6=E6-E7
Q6Q6=FIXED((M6/8)-(M7/8),1)
E6E6=R68/8
E7E7=AA7+AB7
M6M6=U68
M7M7=(AC7*8)+(AD7*8)
O7O7=M7/8
C11C11=IF(OR($H$5=2,$H$5=-4),1," ")
D11D11=IF(OR($H$5=3,$H$5=-3),1,IF(C11=1,C11+1," "))
E11E11=IF(OR(H5=4,H5=-2),1,IF(D11=1,D11+1,IF(D11=2,D11+1," ")))
F11F11=IF(OR($H$5=5,$H$5=-1),1,IF(E11=1,E11+1,IF(E11=2,E11+1,IF(E11=3,E11+1," "))))
G11G11=IF(OR(AND($H$5=6,U3>C5),AND($H$5=0,U3<=C5)),1,IF(F11=1,F11+1,IF(F11=2,F11+1,IF(F11=3,F11+1,IF(F11=4,F11+1," ")))))
H11H11=IF(OR(AND($H$5=0,U3>C5),AND($H$5=-6,U3<C5)),1,IF(G11=1,G11+1,IF(G11=2,G11+1,IF(G11=3,G11+1,IF(G11=4,G11+1,IF(G11=5,G11+1," "))))))
I11I11=IF(OR($H$5=1,$H$5=-5),1,IF(H11=1,H11+1,IF(H11=2,H11+1,IF(H11=3,H11+1,IF(H11=4,H11+1,IF(H11=5,H11+1,IF(H11=6,H11+1," ")))))))
K11,K56,S41,K41,S26K11=IF(I19=31,1," ")
L11,L56,T41,L41,T26L11=IF(C21=31,1,IF(K11=1,K11+1," "))
M11,M56,U41,M41M11=IF(D21=31,1,IF(L11=1,L11+1,IF(L11=2,L11+1," ")))
N11,N56,V41,N41N11=IF(E19=31,1,IF(M11=1,M11+1,IF(M11=2,M11+1,IF(M11=3,M11+1," "))))
O11,O56,W41,O41O11=IF(F19=31,1,IF(N11=1,N11+1,IF(N11=2,N11+1,IF(N11=3,N11+1,IF(N11=4,N11+1," ")))))
P11,P56,X41,P41P11=IF(G19=31,1,IF(O11=1,O11+1,IF(O11=2,O11+1,IF(O11=3,O11+1,IF(O11=4,O11+1,IF(O11=5,O11+1," "))))))
Q11,Q56,Y41,Q41Q11=IF(H19=31,1,IF(P11=1,P11+1,IF(P11=2,P11+1,IF(P11=3,P11+1,IF(P11=4,P11+1,IF(P11=5,P11+1,IF(P11=6,P11+1," ")))))))
S11S11=IF(AND(Q17=22,Q19=29),1," ")
T11T11=IF(AND(K19=28,L19<>29),1,IF(K19=29,1,IF(S11=1,S11+1," ")))
U11U11=IF(AND(L19=28,M19<>29),1,IF(L19=29,1,IF(T11=1,T11+1,IF(T11=2,T11+1," "))))
V11V11=IF(AND(M19=28,N19<>29),1,IF(M19=29,1,IF(U11=1,U11+1,IF(U11=2,U11+1,IF(U11=3,U11+1," ")))))
W11W11=IF(AND(N19=28,O19<>29),1,IF(N19=29,1,IF(V11=1,V11+1,IF(V11=2,V11+1,IF(V11=3,V11+1,IF(V11=4,V11+1," "))))))
X11X11=IF(AND(O19=28,P19<>29),1,IF(O19=29,1,IF(W11=1,W11+1,IF(W11=2,W11+1,IF(W11=3,W11+1,IF(W11=4,W11+1,IF(W11=5,W11+1," ")))))))
Y11Y11=IF(AND(P19=28,Q19<>29),1,IF(P19=29,1,IF(X11=1,X11+1,IF(X11=2,X11+1,IF(X11=3,X11+1,IF(X11=4,X11+1,IF(X11=5,X11+1,IF(X11=6,X11+1," "))))))))
C13,S64,K64,C64,S62,K62,C62,S60,K60,C60,S58,K58,C58,S49,K49,C49,S47,K47,C47,S45,K45,C45,S43,K43,C43,S34,K34,C34,S32,K32,C32,S30,K30,C30,S28,K28,C28,S19,C19,S17,K17,C17,S15,K15,C15,S13,K13C13=I11+1
T64,L64,D64:E64,T62:Y62,L62:Q62,D62:I62,T60:Y60,L60:Q60,D60:I60,T58:Y58,L58:Q58,D58:I58,T49,L49:M49,D49:E49,T47:Y47,L47:Q47,D47:I47,T45:Y45,L45:Q45,D45:I45,T43:Y43,L43:Q43,D43:I43,T34,L34:M34,D34,T32:Y32,L32:Q32,D32:I32,T30:Y30,L30:Q30,D30:I30,T28:Y28D13=C13+1
E19,U64,F64,N49,F49,N34,U19E19=IF(D19=31," ",D19+1)
D21:I21,F19:I19F19=IF(OR(E19=31,E19= " ")," ",E19+1)
K19K19=IF(Q17<28,Q17+1,IF(AND(MOD($U$3,4)=0,Q17<=28),29," "))
L19:Q19L19=IF(K19<28,K19+1,IF(AND(MOD($U$3,4)=0,K19<=28),29," "))
T66,D66,V64:Y64,G64:I64,L51,D51,O49:Q49,G49:I49,L36:Q36,O34:Q34,T21:Y21,V19:Y19V19=IF(OR(U19=31,U19=" ")," ",U19+1)
C21C21=IF(OR(I19=31,I19= " ")," ",I19+1)
S21,S66,C66,K51,C51,K36S21=IF(OR(Y19=31,Y19=" ")," ",Y19+1)
C26C26=IF(Y19=31,1," ")
D26D26=IF(S21=31,1,IF(C26=1,C26+1," "))
E26E26=IF(T21=31,1,IF(D26=1,D26+1,IF(D26=2,D26+1," ")))
F26F26=IF(OR(U19=31,U21=31),1,IF(E26=1,E26+1,IF(E26=2,E26+1,IF(E26=3,E26+1," "))))
G26G26=IF(OR(V19=31,V21=31),1,IF(F26=1,F26+1,IF(F26=2,F26+1,IF(F26=3,F26+1,IF(F26=4,F26+1," ")))))
H26H26=IF(OR(W19=31,W21=31),1,IF(G26=1,G26+1,IF(G26=2,G26+1,IF(G26=3,G26+1,IF(G26=4,G26+1,IF(G26=5,G26+1," "))))))
I26I26=IF(OR(X19=31,X21=31),1,IF(H26=1,H26+1,IF(H26=2,H26+1,IF(H26=3,H26+1,IF(H26=4,H26+1,IF(H26=5,H26+1,IF(H26=6,H26+1," ")))))))
K26,S56K26=IF(I34=30,1," ")
L26,T56L26=IF(C36=30,1,IF(K26=1,K26+1," "))
M26M26=IF(OR(D34=30,D36=30),1,IF(L26=1,L26+1,IF(L26=2,L26+1," ")))
N26,V56N26=IF(E34=30,1,IF(M26=1,M26+1,IF(M26=2,M26+1,IF(M26=3,M26+1," "))))
O26,W56O26=IF(F34=30,1,IF(N26=1,N26+1,IF(N26=2,N26+1,IF(N26=3,N26+1,IF(N26=4,N26+1," ")))))
P26,X56P26=IF(G34=30,1,IF(O26=1,O26+1,IF(O26=2,O26+1,IF(O26=3,O26+1,IF(O26=4,O26+1,IF(O26=5,O26+1," "))))))
Q26,Y56Q26=IF(H34=30,1,IF(P26=1,P26+1,IF(P26=2,P26+1,IF(P26=3,P26+1,IF(P26=4,P26+1,IF(P26=5,P26+1,IF(P26=6,P26+1," ")))))))
U26U26=IF(OR(L34=31,L36=31),1,IF(T26=1,T26+1,IF(T26=2,T26+1," ")))
V26V26=IF(OR(M34=31,M36=31),1,IF(U26=1,U26+1,IF(U26=2,U26+1,IF(U26=3,U26+1," "))))
W26W26=IF(OR(N34=31,N36=31),1,IF(V26=1,V26+1,IF(V26=2,V26+1,IF(V26=3,V26+1,IF(V26=4,V26+1," ")))))
X26X26=IF(OR(O34=31,O36=31),1,IF(W26=1,W26+1,IF(W26=2,W26+1,IF(W26=3,W26+1,IF(W26=4,W26+1,IF(W26=5,W26+1," "))))))
Y26Y26=IF(OR(P34=31,P36=31),1,IF(X26=1,X26+1,IF(X26=2,X26+1,IF(X26=3,X26+1,IF(X26=4,X26+1,IF(X26=5,X26+1,IF(X26=6,X26+1," ")))))))
N64:Q64,U49:Y49,T36,U34:Y34,E34:I34E34=IF(OR(D34=30,D34=" ")," ",D34+1)
C36,K66,S51,S36C36=IF(OR(I34=30,I34=" ")," ",I34+1)
C41,C56C41=IF(Y34=30,1," ")
D41,D56D41=IF(S36=30,1,IF(C41=1,C41+1," "))
E41,E56E41=IF(T34=30,1,IF(D41=1,D41+1,IF(D41=2,D41+1," ")))
F41,F56F41=IF(U34=30,1,IF(E41=1,E41+1,IF(E41=2,E41+1,IF(E41=3,E41+1," "))))
G41,G56G41=IF(V34=30,1,IF(F41=1,F41+1,IF(F41=2,F41+1,IF(F41=3,F41+1,IF(F41=4,F41+1," ")))))
H41,H56H41=IF(W34=30,1,IF(G41=1,G41+1,IF(G41=2,G41+1,IF(G41=3,G41+1,IF(G41=4,G41+1,IF(G41=5,G41+1," "))))))
I41,I56I41=IF(X34=30,1,IF(H41=1,H41+1,IF(H41=2,H41+1,IF(H41=3,H41+1,IF(H41=4,H41+1,IF(H41=5,H41+1,IF(H41=6,H41+1," ")))))))
U56U56=IF(L64=30,1,IF(T56=1,T56+1,IF(T56=2,T56+1," ")))
M64M64=IF(L64=30," ",L64+1)
R68R68=AI74
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W68:X68Cell Value=0textYES
S47:Y47,K13:Q13,C58:I58,K58:Q58,C60:I60,S15:Y15,K17:Q17,K60:Q60,K28:Q28,K32:Q32,S30:Y30,C62:I62,S43:Y43,S28:Y28,K47:Q47,C47:I47,K30:Q30,S45:Y45,C43:I43,K43:Q43,S17:Y17,K62:Q62,C45:I45,S32:Y32,K15:Q15,S13:Y13,C28:I28,K45:Q45,C30:I30,C32:I32Expression=C13=""textYES
Y68Cell Value=0textYES
D10:H10,L10:P10,T10:X10,D18:H18,L18:P18,T18:X18,D12:H12,L12:P12,T12:X12,D14:H14,L14:P14,T14:X14,D16:H16,L16:P16,T16:X16,D20:H20,T65:X65,T20:X20,D25:H25,L25:P25,T25:X25,D27:H27,L27:P27,T27:X27,D29:H29,L29:P29,T29:X29,D31:H31,L31:P31,T31:X31,D33:H33,L33:P33Expression=IF(D10="T",TRUE,IF(D10="T4",TRUE,FALSE))textYES
D10:H10,L10:P10,T10:X10,D18:H18,L18:P18,T18:X18,D12:H12,L12:P12,T12:X12,D14:H14,L14:P14,T14:X14,D16:H16,L16:P16,T16:X16,D20:H20,T65:X65,T20:X20,D25:H25,L25:P25,T25:X25,D27:H27,L27:P27,T27:X27,D29:H29,L29:P29,T29:X29,D31:H31,L31:P31,T31:X31,D33:H33,L33:P33Expression=IF(D10="P",TRUE,IF(D10="P4",TRUE,FALSE))textYES
D10:H10,L10:P10,T10:X10,D18:H18,L18:P18,T18:X18,D12:H12,L12:P12,T12:X12,D14:H14,L14:P14,T14:X14,D16:H16,L16:P16,T16:X16,D20:H20,T65:X65,T20:X20,D25:H25,L25:P25,T25:X25,D27:H27,L27:P27,T27:X27,D29:H29,L29:P29,T29:X29,D31:H31,L31:P31,T31:X31,D33:H33,L33:P33Expression=IF(D10="S",TRUE,IF(D10="S4",TRUE,FALSE))textYES
K11:P11,C11:H11,S11:X11,K26:P26,C26:H26,S26:X26,K41:P41,C41:H41,S41:X41,K56:P56,C56:H56,S56:X56,F19:I19,C21:I22,S21:Y22,V19:Y19,C51:I51,N34:Q34,K36:Q37,S36:Y36,F49:I49,C36:I36,N49:Q49,K51:Q51,S51:Y51,F64:I64,C66:I66,M64:Q64,K66:Q66,V64:Y64,K19:Q19,S66:Y66Cell Value=" "textYES
 
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

"Add 2.57 Hours Every Sunday" thread, but it's not working quite right.
Ctrl+Shft+Enter
Excel Formula:
=SUMPRODUCT(--(WEEKDAY(DATE($B$1,1,1)+TRANSPOSE(ROW(INDIRECT("1:"&(DATE($B$1,12,31)+1-DATE($B$1,1,1))))-1),17)=1))*2.57
 

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thanks for replying and giving me a formula to work with and a spreadsheet to refer to! I really appreciate the time and effort you put into this! I do have some questions though in order to fully understand them (there’s a LOT of functions used that I rarely if ever have used).

Where does the “17” come from in the formula in X7? Is that referring to cell C1? And where does that number in C7 come from?

I’m seeing that the formula in cell U7 simplifies counting the number of hours taken. I had made it more complicated by having four hidden columns keeping track of each time “T,” “T4,” “S,” or “S4” appears in the row and summing those amounts. So now, since I also track my Sick time, I can modify that formula you gave and use it in cells D7 and L7. Thanks! One question though – how do I modify the formula to include half days? Currently, when I take a half day (4 hours), I enter T4. The formula you provided only looks at T. I tried AND and OR, but I’m apparently not putting them in the right place because the answer is incorrect.

So I understand where the Balance Used (cell U7) portion of the formula comes from, and I changed the “2.57” part of the formula to refer to a cell reference (per my initial post) since it can be different based upon how many years one has been with the company (I share this spreadsheet with my coworkers). But where does the Beginning Balance get plugged in? I see that U7 shows the balance for the year based upon the time of year, but the beginning balance isn’t zero on 1/1. Since I don’t need the “Balance per 14” information, would I just change that title to Beginning balance, copy the formula from cell X7 and add the cell reference of where the beginning balance is to the beginning of the formula (=cell reference+sumproduct…)?

Oh, one more thing – a slight correction on your spreadsheet – cell D6 should be the number of days available not the number of hours. I wish I had 200 days off per year, but alas, I don’t!
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Greetings

"X7" 1 or 17 weekday Return_Type
"X7" count Sun days
"U5" T4
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Perpetual Balance Update.xlsx
CDEFGHIJKLMNOPQRSTUVWXY
12020
2
3
4
51672Total
6PTO days:0Days Left:0Sick Time0Days Left:0.0Balance per 14392Defered300
7Taken0DaysTaken0Hours0daysSubtracted24Per Sun134
8JANUARYFEBRUARYMARCH
9SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
10TTTT4T4T4T4
11    1231234567 123456
12SSS
134567891089101112131478910111213
14S4S4
15111213141516171516171819202114151617181920
16
17181920212223242223242526272821222324252627
18
192526272829303129      28293031   
20
21              
22
23APRILMAYJUNE
24SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
25
26    123      1  12345
27
284567891023456786789101112
29
3011121314151617910111213141513141516171819
31
32181920212223241617181920212220212223242526
33
34252627282930 2324252627282927282930   
35
36 3031       
37
38JULYAUGUSTSEPTEMBER
39SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
40
41    1231234567   1234
42
4345678910891011121314567891011
44
45111213141516171516171819202112131415161718
46
47181920212223242223242526272819202122232425
48
4925262728293031293031    2627282930  
50
51     
52
53OCTOBERNOVEMBERDECEMBER
54SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
55
56     12 123456   1234
57
58345678978910111213567891011
59
60101112131415161415161718192012131415161718
61
62171819202122232122232425262719202122232425
63
6424252627282930282930    262728293031 
65
6631    
67KeyInitial # of days available
68PaydayHolidayETO balance0Sick:
69
Vac
Cell Formulas
RangeFormula
W5W5=(COUNTIF(C8:Y66,"T")*8)+(COUNTIF(C8:Y66,"T4")*4)+(COUNTIF(C8:Y66,"S")*8)+(COUNTIF(C8:Y66,"S4")*4)
I6I6=E6-E7
Q6Q6=FIXED((M6/8)-(M7/8),1)
E6E6=R68/8
E7E7=AA7+AB7
M6M6=U68
M7M7=(AC7*8)+(AD7*8)
O7O7=M7/8
V5V5=COUNTIF(C8:Y66,"T4")*4
V6V6=((DATE($C$1,12,31)-DATE($C$1,1,1)+1)/14)*15
V7V7=COUNTIF(C8:Y66,"T")*8
Y7Y7=SUMPRODUCT(--(WEEKDAY(DATE($C$1,1,1)+TRANSPOSE(ROW(INDIRECT("1:"&(DATE($C$1,12,31)+1-DATE($C$1,1,1))))-1),17)=1))*2.57
C11C11=IF(OR($H$5=2,$H$5=-4),1," ")
D11D11=IF(OR($H$5=3,$H$5=-3),1,IF(C11=1,C11+1," "))
E11E11=IF(OR(H5=4,H5=-2),1,IF(D11=1,D11+1,IF(D11=2,D11+1," ")))
F11F11=IF(OR($H$5=5,$H$5=-1),1,IF(E11=1,E11+1,IF(E11=2,E11+1,IF(E11=3,E11+1," "))))
G11G11=IF(OR(AND($H$5=6,U3>C5),AND($H$5=0,U3<=C5)),1,IF(F11=1,F11+1,IF(F11=2,F11+1,IF(F11=3,F11+1,IF(F11=4,F11+1," ")))))
H11H11=IF(OR(AND($H$5=0,U3>C5),AND($H$5=-6,U3<C5)),1,IF(G11=1,G11+1,IF(G11=2,G11+1,IF(G11=3,G11+1,IF(G11=4,G11+1,IF(G11=5,G11+1," "))))))
I11I11=IF(OR($H$5=1,$H$5=-5),1,IF(H11=1,H11+1,IF(H11=2,H11+1,IF(H11=3,H11+1,IF(H11=4,H11+1,IF(H11=5,H11+1,IF(H11=6,H11+1," ")))))))
K11,K56,S41,K41,S26K11=IF(I19=31,1," ")
L11,L56,T41,L41,T26L11=IF(C21=31,1,IF(K11=1,K11+1," "))
M11,M56,U41,M41M11=IF(D21=31,1,IF(L11=1,L11+1,IF(L11=2,L11+1," ")))
N11,N56,V41,N41N11=IF(E19=31,1,IF(M11=1,M11+1,IF(M11=2,M11+1,IF(M11=3,M11+1," "))))
O11,O56,W41,O41O11=IF(F19=31,1,IF(N11=1,N11+1,IF(N11=2,N11+1,IF(N11=3,N11+1,IF(N11=4,N11+1," ")))))
P11,P56,X41,P41P11=IF(G19=31,1,IF(O11=1,O11+1,IF(O11=2,O11+1,IF(O11=3,O11+1,IF(O11=4,O11+1,IF(O11=5,O11+1," "))))))
Q11,Q56,Y41,Q41Q11=IF(H19=31,1,IF(P11=1,P11+1,IF(P11=2,P11+1,IF(P11=3,P11+1,IF(P11=4,P11+1,IF(P11=5,P11+1,IF(P11=6,P11+1," ")))))))
S11S11=IF(AND(Q17=22,Q19=29),1," ")
T11T11=IF(AND(K19=28,L19<>29),1,IF(K19=29,1,IF(S11=1,S11+1," ")))
U11U11=IF(AND(L19=28,M19<>29),1,IF(L19=29,1,IF(T11=1,T11+1,IF(T11=2,T11+1," "))))
V11V11=IF(AND(M19=28,N19<>29),1,IF(M19=29,1,IF(U11=1,U11+1,IF(U11=2,U11+1,IF(U11=3,U11+1," ")))))
W11W11=IF(AND(N19=28,O19<>29),1,IF(N19=29,1,IF(V11=1,V11+1,IF(V11=2,V11+1,IF(V11=3,V11+1,IF(V11=4,V11+1," "))))))
X11X11=IF(AND(O19=28,P19<>29),1,IF(O19=29,1,IF(W11=1,W11+1,IF(W11=2,W11+1,IF(W11=3,W11+1,IF(W11=4,W11+1,IF(W11=5,W11+1," ")))))))
Y11Y11=IF(AND(P19=28,Q19<>29),1,IF(P19=29,1,IF(X11=1,X11+1,IF(X11=2,X11+1,IF(X11=3,X11+1,IF(X11=4,X11+1,IF(X11=5,X11+1,IF(X11=6,X11+1," "))))))))
C13,S64,K64,C64,S62,K62,C62,S60,K60,C60,S58,K58,C58,S49,K49,C49,S47,K47,C47,S45,K45,C45,S43,K43,C43,S34,K34,C34,S32,K32,C32,S30,K30,C30,S28,K28,C28,S19,C19,S17,K17,C17,S15,K15,C15,S13,K13C13=I11+1
T64,L64,D64:E64,T62:Y62,L62:Q62,D62:I62,T60:Y60,L60:Q60,D60:I60,T58:Y58,L58:Q58,D58:I58,T49,L49:M49,D49:E49,T47:Y47,L47:Q47,D47:I47,T45:Y45,L45:Q45,D45:I45,T43:Y43,L43:Q43,D43:I43,T34,L34:M34,D34,T32:Y32,L32:Q32,D32:I32,T30:Y30,L30:Q30,D30:I30,T28:Y28D13=C13+1
E19,U64,F64,N49,F49,N34,U19E19=IF(D19=31," ",D19+1)
D21:I21,F19:I19F19=IF(OR(E19=31,E19= " ")," ",E19+1)
K19K19=IF(Q17<28,Q17+1,IF(AND(MOD($U$3,4)=0,Q17<=28),29," "))
L19:Q19L19=IF(K19<28,K19+1,IF(AND(MOD($U$3,4)=0,K19<=28),29," "))
T66,D66,V64:Y64,G64:I64,L51,D51,O49:Q49,G49:I49,L36:Q36,O34:Q34,T21:Y21,V19:Y19V19=IF(OR(U19=31,U19=" ")," ",U19+1)
C21C21=IF(OR(I19=31,I19= " ")," ",I19+1)
S21,S66,C66,K51,C51,K36S21=IF(OR(Y19=31,Y19=" ")," ",Y19+1)
C26C26=IF(Y19=31,1," ")
D26D26=IF(S21=31,1,IF(C26=1,C26+1," "))
E26E26=IF(T21=31,1,IF(D26=1,D26+1,IF(D26=2,D26+1," ")))
F26F26=IF(OR(U19=31,U21=31),1,IF(E26=1,E26+1,IF(E26=2,E26+1,IF(E26=3,E26+1," "))))
G26G26=IF(OR(V19=31,V21=31),1,IF(F26=1,F26+1,IF(F26=2,F26+1,IF(F26=3,F26+1,IF(F26=4,F26+1," ")))))
H26H26=IF(OR(W19=31,W21=31),1,IF(G26=1,G26+1,IF(G26=2,G26+1,IF(G26=3,G26+1,IF(G26=4,G26+1,IF(G26=5,G26+1," "))))))
I26I26=IF(OR(X19=31,X21=31),1,IF(H26=1,H26+1,IF(H26=2,H26+1,IF(H26=3,H26+1,IF(H26=4,H26+1,IF(H26=5,H26+1,IF(H26=6,H26+1," ")))))))
K26,S56K26=IF(I34=30,1," ")
L26,T56L26=IF(C36=30,1,IF(K26=1,K26+1," "))
M26M26=IF(OR(D34=30,D36=30),1,IF(L26=1,L26+1,IF(L26=2,L26+1," ")))
N26,V56N26=IF(E34=30,1,IF(M26=1,M26+1,IF(M26=2,M26+1,IF(M26=3,M26+1," "))))
O26,W56O26=IF(F34=30,1,IF(N26=1,N26+1,IF(N26=2,N26+1,IF(N26=3,N26+1,IF(N26=4,N26+1," ")))))
P26,X56P26=IF(G34=30,1,IF(O26=1,O26+1,IF(O26=2,O26+1,IF(O26=3,O26+1,IF(O26=4,O26+1,IF(O26=5,O26+1," "))))))
Q26,Y56Q26=IF(H34=30,1,IF(P26=1,P26+1,IF(P26=2,P26+1,IF(P26=3,P26+1,IF(P26=4,P26+1,IF(P26=5,P26+1,IF(P26=6,P26+1," ")))))))
U26U26=IF(OR(L34=31,L36=31),1,IF(T26=1,T26+1,IF(T26=2,T26+1," ")))
V26V26=IF(OR(M34=31,M36=31),1,IF(U26=1,U26+1,IF(U26=2,U26+1,IF(U26=3,U26+1," "))))
W26W26=IF(OR(N34=31,N36=31),1,IF(V26=1,V26+1,IF(V26=2,V26+1,IF(V26=3,V26+1,IF(V26=4,V26+1," ")))))
X26X26=IF(OR(O34=31,O36=31),1,IF(W26=1,W26+1,IF(W26=2,W26+1,IF(W26=3,W26+1,IF(W26=4,W26+1,IF(W26=5,W26+1," "))))))
Y26Y26=IF(OR(P34=31,P36=31),1,IF(X26=1,X26+1,IF(X26=2,X26+1,IF(X26=3,X26+1,IF(X26=4,X26+1,IF(X26=5,X26+1,IF(X26=6,X26+1," ")))))))
N64:Q64,U49:Y49,T36,U34:Y34,E34:I34E34=IF(OR(D34=30,D34=" ")," ",D34+1)
C36,K66,S51,S36C36=IF(OR(I34=30,I34=" ")," ",I34+1)
C41,C56C41=IF(Y34=30,1," ")
D41,D56D41=IF(S36=30,1,IF(C41=1,C41+1," "))
E41,E56E41=IF(T34=30,1,IF(D41=1,D41+1,IF(D41=2,D41+1," ")))
F41,F56F41=IF(U34=30,1,IF(E41=1,E41+1,IF(E41=2,E41+1,IF(E41=3,E41+1," "))))
G41,G56G41=IF(V34=30,1,IF(F41=1,F41+1,IF(F41=2,F41+1,IF(F41=3,F41+1,IF(F41=4,F41+1," ")))))
H41,H56H41=IF(W34=30,1,IF(G41=1,G41+1,IF(G41=2,G41+1,IF(G41=3,G41+1,IF(G41=4,G41+1,IF(G41=5,G41+1," "))))))
I41,I56I41=IF(X34=30,1,IF(H41=1,H41+1,IF(H41=2,H41+1,IF(H41=3,H41+1,IF(H41=4,H41+1,IF(H41=5,H41+1,IF(H41=6,H41+1," ")))))))
U56U56=IF(L64=30,1,IF(T56=1,T56+1,IF(T56=2,T56+1," ")))
M64M64=IF(L64=30," ",L64+1)
R68R68=AI74
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,454
Messages
5,596,228
Members
414,047
Latest member
debbos

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.
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 "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
Top