Help with Random Formulas

K

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
Hello,

So I am building somewhat of a gantt chart. The following is a snap shot. What i am trying to do is bascially put a number "8" in this example in every day for that project. I do have holidays listed in a seperate holiday tab. So while i have this formula working i cannot figure out how to keep a holiday column blank and continue placing "8" until the duration is hit. Any help would be appreciated.

SRI Schedule Prototype -Updated Cond.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCN
1DurationContractSubmittalsLabor Req.Emp. Count88888888888888888888888888888888888888888888888888888888888888888888888888888888
2JanJanJanJanFebFebFebFebMarMarMarMarMarAprAprApr
3Job #Job NameRoof SystemPMSuptStart DateEnd DateContractor234569101112131617181920232425262730311236789101314151617202122232427281236789101314151617202122232427282930313456710111213141718192021
4Testing Tester                                                                                
53376Project RiverRhinobondJSBM1/1/20234/25/202381YAGClayco88888888888888888888888888888888888888888888888888888888888888888888888888888888
63411Dhamer Drive Spec BldgRhinobondJSBM1/24/2023NNSMeridian                                                                                
73416Bridge McCook Bldg #1EPDM/BallastJSBM5/17/2023YNSARCO                                                                                
Schedule
Cell Formulas
RangeFormula
M1:CN1M1=IF(SUM(M4:M27)=0,"",SUM(M4:M27))
M2,R2,W2,AB2,AG2,AL2,AQ2,AV2,BA2,BF2,BK2,BP2,BU2,BZ2,CE2,CJ2M2=CHOOSE(MONTH(O3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
M3M3=WORKDAY(Holidays!C3,1)
N3:CN3N3=WORKDAY(M3,1)
M4:CN7M4=IFERROR(IF(AND(M$3>=$F4,M$3<=WORKDAY($F4,$H4-1)),8,""),"")
G5G5=WORKDAY(F5,H5,Holidays!I4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M3:JQ27Expression=COUNTIFS(Holidays!$F$3:$F$34,M$3)textNO
4:50Expression=AND(NOT(ISBLANK($B4)),ISBLANK($A4))textNO
H1:H3,I:IExpression=AND(NOT(ISBLANK($A1)),$I1="N")textNO
H1:H3,I:IExpression=AND(NOT(ISBLANK($A1)),$I1="Y")textNO
J:JExpression=AND(NOT(ISBLANK($A1)),$J1="S")textNO
J:JExpression=AND(NOT(ISBLANK($A1)),$J1="NS")textNO
J:JExpression=AND(NOT(ISBLANK($A1)),$J1="A")textNO
Cells with Data Validation
CellAllowCriteria
I4:I45List=Holidays!$Z$2:$Z$3
J4:J45List=Holidays!$AA$2:$AA$4


Bonus - For workday formula why doesnt this work? I have some formulas in column F that i use to reference holidays but it errors out due to the """" (blank) cells based on the formulas. Again any help is much appreciated!

SRI Schedule Prototype -Updated Cond.xlsx
ABCDEF
1YEAR2023
2HolidaysWhen do they occur?Actual DateIncludeDay of WeekCalc'd Date
3New Year's DayJanuary 1st1/1/2023YSunday1/2/2023
4Martin Luther King Day3rd Monday in January1/16/2023NMonday 
5President's Day3rd Monday in February2/20/2023NMonday 
6Easter Sunday Western Observation4/9/2023NSunday 
7Mother's Day2nd Sunday in May5/14/2023NSunday 
8Memorial DayLast Monday in May5/29/2023YMonday5/29/2023
9Father's Day3rd Sunday in June6/18/2023NSunday 
10Independence DayJuly 4th7/4/2023YTuesday7/4/2023
11Labor Day1st Monday in September9/4/2023YMonday9/4/2023
12Columbus Day2nd Monday in October10/9/2023NMonday 
13Veteran's DayNovember 11th11/11/2023NSaturday 
14Thanksgiving Day4th Thursday in November11/23/2023YThursday11/23/2023
15Day After Thanksgiving4th Friday in November11/24/2023NFriday 
16Christmas EveDecember 24th12/24/2023NSunday 
17Christmas DayDecember 25th12/25/2023YMonday12/25/2023
18YEAR2024
19HolidaysWhen do they occur?Actual DateIncludeDay of WeekCalc'd Date
20New Year's DayJanuary 1st1/1/2024YMonday1/1/2024
21Martin Luther King Day3rd Monday in January1/15/2024NMonday 
22President's Day3rd Monday in February2/19/2024NMonday 
23Easter Sunday Western Observation3/31/2024NSunday 
24Mother's Day2nd Sunday in May5/12/2024NSunday 
25Memorial DayLast Monday in May5/27/2024YMonday5/27/2024
26Father's Day3rd Sunday in June6/16/2024NSunday 
27Independence DayJuly 4th7/4/2024YThursday7/4/2024
28Labor Day1st Monday in September9/2/2024YMonday9/2/2024
29Columbus Day2nd Monday in October10/14/2024NMonday 
30Veteran's DayNovember 11th11/11/2024NMonday 
31Thanksgiving Day4th Thursday in November11/28/2024YThursday11/28/2024
32Day After Thanksgiving4th Friday in November11/29/2024NFriday 
33Christmas EveDecember 24th12/24/2024NTuesday 
34Christmas DayDecember 25th12/25/2024YWednesday12/25/2024
Holidays
Cell Formulas
RangeFormula
C3,C20C3=DATE(F1,1,1)
C4,C21C4=DATE(F1,1,1)+14+CHOOSE(WEEKDAY(DATE(F1,1,1)),1,0,6,5,4,3,2)
C5,C22C5=DATE(F1,2,1)+14+CHOOSE(WEEKDAY(DATE(F1,2,1)),1,0,6,5,4,3,2)
C6,C23C6=FLOOR("5/"&DAY(MINUTE(F1/38)/2+56)&"/"&F1,7)-34
C7,C24C7=DATE(F1,5,1)+7+CHOOSE(WEEKDAY(DATE(F1,5,1)),0,6,5,4,3,2,1)
C8,C25C8=DATE((F1),5,CHOOSE(WEEKDAY(DATE((F1),5,1)),30,29,28,27,26,25,31))
C9,C26C9=DATE(F1,6,1)+14+CHOOSE(WEEKDAY(DATE(F1,6,1)),0,6,5,4,3,2,1)
C10,C27C10=DATE(F1,7,4)
C11,C28C11=DATE(F1,9,1)+CHOOSE(WEEKDAY(DATE(F1,9,1)),1,0,6,5,4,3,2)
C12,C29C12=DATE(F1,10,1)+7+CHOOSE(WEEKDAY(DATE(F1,10,1)),1,0,6,5,4,3,2)
C13,C30C13=DATE(F1,11,11)
C14,C31C14=DATE(F1,11,1)+21+CHOOSE(WEEKDAY(DATE(F1,11,1)),4,3,2,1,0,6,5)
C15,C32C15=C14+1
C16,C33C16=DATE(F1,12,24)
C17,C34C17=DATE(F1,12,25)
E3:E17,E20:E34E3=TEXT(C3,"dddd")
F3:F17,F20:F34F3=IFERROR(IF(OR(D3="N",D3=""),"",IF(AND(D3="Y",WEEKDAY(C3,2)=6),WORKDAY(C3,-1),IF(AND(D3="Y",WEEKDAY(C3,2)=7),WORKDAY(C3,1),C3))),"")
F18F18=F1+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F20:F34Expression=$D20="Y"textNO
F3:F17Expression=$D3="Y"textNO
E20:E34Expression=$D20="Y"textNO
A20:D34Expression=$D20="Y"textNO
E3:E17Expression=$D3="Y"textNO
A3:D17Expression=$D3="Y"textNO
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
keef2 said:
So while i have this formula working i cannot figure out how to keep a holiday column blank and continue placing "8" until the duration is hit.
Click to expand...
Just for my understanding, as I'm still not clear -

In cell W5 on Schedule sheet you want formula to return Blank as it is a holiday marked in Holiday sheet?

keef2 said:
Bonus - For workday formula why doesnt this work? I have some formulas in column F that i use to reference holidays but it errors out due to the """" (blank) cells based on the formulas. Again any help is much appreciated!
Click to expand...
As of Workday Formula - I can't promise. I'm not at all good with Date formulae (Never needed them to use) - Still if you can Share XL2BB of the row/area where it gives error probably me or someone better than me will help you out on that.
 
Upvote 0
holiday range :
Holidays!$C$3:$C$34
There is a cell with text value (C19) which cause error. Remove it to be blank.
Then, all WORKDAY can be added holiday range

G5:
=WORKDAY(F5,H5,Holidays!$C$3:$C$34)

M3:
=WORKDAY(Holidays!C3,1,Holidays!$C$3:$C$34)

N3, drag accross
=WORKDAY(M3,1,Holidays!$C$3:$C$34)

then M5, drag accross:
=IFERROR(IF(AND(M$3>=$F5,M$3<=$G5),8,""),"")
 
Upvote 0
SanjayGMusafir said:
Just for my understanding, as I'm still not clear -

In cell W5 on Schedule sheet you want formula to return Blank as it is a holiday marked in Holiday sheet?


As of Workday Formula - I can't promise. I'm not at all good with Date formulae (Never needed them to use) - Still if you can Share XL2BB of the row/area where it gives error probably me or someone better than me will help you out on that.
Click to expand...
It gives the error because the formula shows """ if i just use F3:F4 as the range it throws the error when selecting this range on the holiday tab.
 
Upvote 0
bebo021999 said:
holiday range :
Holidays!$C$3:$C$34
There is a cell with text value (C19) which cause error. Remove it to be blank.
Then, all WORKDAY can be added holiday range

G5:
=WORKDAY(F5,H5,Holidays!$C$3:$C$34)

M3:
=WORKDAY(Holidays!C3,1,Holidays!$C$3:$C$34)

N3, drag accross
=WORKDAY(M3,1,Holidays!$C$3:$C$34)

then M5, drag accross:
=IFERROR(IF(AND(M$3>=$F5,M$3<=$G5),8,""),"")
Click to expand...
This still didnt work. Seems its something to do with the formula i have i even tried to add Value() function with no success. Curious if anyone can solve this thanks for the ideas tho.
 
Upvote 0
My work around so far was filter function. Another question i have that i forget is when i reference the filtered section AB in a formula what does adding # at the end of the range do again? Here is a snap shot of what i mean see G5 formula:
SRI Schedule Prototype.xlsx
ABCDEFG
3Job #Job NameRoof SystemPMSuptStart DateEnd Date
4Testing Tester
53376Project RiverRhinobondJSBM9/12/20221/6/2023
Schedule
Cell Formulas
RangeFormula
G5G5=IF(H5="","",WORKDAY(F5,H5,Holidays!$AB$1#))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
4:50Expression=AND(NOT(ISBLANK($B4)),ISBLANK($A4))textNO


Here is the entire Holidays tab.
SRI Schedule Prototype.xlsx
ABCDEFAB
1YEAR202212/31/2021
2HolidaysWhen do they occur?Actual DateIncludeDay of WeekCalc'd Date5/30/2022
3New Year's DayJanuary 1st1/1/2022YSaturday12/31/20217/4/2022
4Martin Luther King Day3rd Monday in January1/17/2022NMonday 9/5/2022
5President's Day3rd Monday in February2/21/2022NMonday 11/24/2022
6Easter Sunday Western Observation4/17/2022NSunday 12/26/2022
7Mother's Day2nd Sunday in May5/8/2022NSunday 1/2/2023
8Memorial DayLast Monday in May5/30/2022YMonday5/30/20225/29/2023
9Father's Day3rd Sunday in June6/19/2022NSunday 7/4/2023
10Independence DayJuly 4th7/4/2022YMonday7/4/20229/4/2023
11Labor Day1st Monday in September9/5/2022YMonday9/5/202211/23/2023
12Columbus Day2nd Monday in October10/10/2022NMonday 12/25/2023
13Veteran's DayNovember 11th11/11/2022NFriday 
14Thanksgiving Day4th Thursday in November11/24/2022YThursday11/24/2022
15Day After Thanksgiving4th Friday in November11/25/2022NFriday 
16Christmas EveDecember 24th12/24/2022NSaturday 
17Christmas DayDecember 25th12/25/2022YSunday12/26/2022
18YEAR2023
19HolidaysWhen do they occur?Actual DateIncludeDay of Week
20New Year's DayJanuary 1st1/1/2023YSunday1/2/2023
21Martin Luther King Day3rd Monday in January1/16/2023NMonday 
22President's Day3rd Monday in February2/20/2023NMonday 
23Easter Sunday Western Observation4/9/2023NSunday 
24Mother's Day2nd Sunday in May5/14/2023NSunday 
25Memorial DayLast Monday in May5/29/2023YMonday5/29/2023
26Father's Day3rd Sunday in June6/18/2023NSunday 
27Independence DayJuly 4th7/4/2023YTuesday7/4/2023
28Labor Day1st Monday in September9/4/2023YMonday9/4/2023
29Columbus Day2nd Monday in October10/9/2023NMonday 
30Veteran's DayNovember 11th11/11/2023NSaturday 
31Thanksgiving Day4th Thursday in November11/23/2023YThursday11/23/2023
32Day After Thanksgiving4th Friday in November11/24/2023NFriday 
33Christmas EveDecember 24th12/24/2023NSunday 
34Christmas DayDecember 25th12/25/2023YMonday12/25/2023
Holidays
Cell Formulas
RangeFormula
AB1:AB12AB1=FILTER(F:F,(D:D="Y"))
C3,C20C3=DATE(F1,1,1)
C4,C21C4=DATE(F1,1,1)+14+CHOOSE(WEEKDAY(DATE(F1,1,1)),1,0,6,5,4,3,2)
C5,C22C5=DATE(F1,2,1)+14+CHOOSE(WEEKDAY(DATE(F1,2,1)),1,0,6,5,4,3,2)
C6,C23C6=FLOOR("5/"&DAY(MINUTE(F1/38)/2+56)&"/"&F1,7)-34
C7,C24C7=DATE(F1,5,1)+7+CHOOSE(WEEKDAY(DATE(F1,5,1)),0,6,5,4,3,2,1)
C8,C25C8=DATE((F1),5,CHOOSE(WEEKDAY(DATE((F1),5,1)),30,29,28,27,26,25,31))
C9,C26C9=DATE(F1,6,1)+14+CHOOSE(WEEKDAY(DATE(F1,6,1)),0,6,5,4,3,2,1)
C10,C27C10=DATE(F1,7,4)
C11,C28C11=DATE(F1,9,1)+CHOOSE(WEEKDAY(DATE(F1,9,1)),1,0,6,5,4,3,2)
C12,C29C12=DATE(F1,10,1)+7+CHOOSE(WEEKDAY(DATE(F1,10,1)),1,0,6,5,4,3,2)
C13,C30C13=DATE(F1,11,11)
C14,C31C14=DATE(F1,11,1)+21+CHOOSE(WEEKDAY(DATE(F1,11,1)),4,3,2,1,0,6,5)
C15,C32C15=C14+1
C16,C33C16=DATE(F1,12,24)
C17,C34C17=DATE(F1,12,25)
E3:E17,E20:E34E3=TEXT(C3,"dddd")
F3:F17,F20:F34F3=IFERROR(IF(OR(D3="N",D3=""),"",IF(AND(D3="Y",WEEKDAY(C3,2)=6),WORKDAY(C3,-1),IF(AND(D3="Y",WEEKDAY(C3,2)=7),WORKDAY(C3,1),C3))),"")
F18F18=F1+1
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F20:F34Expression=$D20="Y"textNO
F3:F17Expression=$D3="Y"textNO
E20:E34Expression=$D20="Y"textNO
A20:D34Expression=$D20="Y"textNO
E3:E17Expression=$D3="Y"textNO
A3:D17Expression=$D3="Y"textNO
 
Upvote 0
SanjayGMusafir said:
Just for my understanding, as I'm still not clear -

In cell W5 on Schedule sheet you want formula to return Blank as it is a holiday marked in Holiday sheet?
Click to expand...
@keef2
Actually you have multiple queries in a post - Though they all can be addressed

I was looking for the reply to above quoted part - If you can clarify.

I have another query that I shall post in next thread under to keep things simple for you and me to understand...
 
Upvote 0
keef2 said:
It gives the error because the formula shows """ if i just use F3:F4 as the range it throws the error when selecting this range on the holiday tab.
Click to expand...
The second clarification I needed is -

where the above it is - I have tried hard to locate """ but didn't find in any formula

If you can paste the formula and cell address of the cell where it is giving error, probably I can think where things might be going wrong.
 
Upvote 0
SanjayGMusafir said:
The second clarification I needed is -

where the above it is - I have tried hard to locate """ but didn't find in any formula

If you can paste the formula and cell address of the cell where it is giving error, probably I can think where things might be going wrong.
Click to expand...
=IFERROR(IF(OR(D3="N",D3=""),"",IF(AND(D3="Y",WEEKDAY(C3,2)=6),WORKDAY(C3,-1),IF(AND(D3="Y",WEEKDAY(C3,2)=7),WORKDAY(C3,1),C3))),"")
 
Upvote 0
SanjayGMusafir said:
@keef2
Actually you have multiple queries in a post - Though they all can be addressed

I was looking for the reply to above quoted part - If you can clarify.

I have another query that I shall post in next thread under to keep things simple for you and me to understand...
Click to expand...
I think i solved this with:
=IF($G5="","",IF(AND(JP$3>=$F5,NOT(COUNTIFS(Holidays!$F$3:$F$34,JP$3)),JP$3<=$G5-1),8,""))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Automating Company Holidays on calendar
Replies
4
Views
1K
saracat2012
S
Z
  • Solved
Conditional Format Date Range
Replies
4
Views
907
zero269
Z
R
  • Solved
Looking for a stop command for my formula
Replies
3
Views
433
Joe4
Joe4
S
  • Question Question
Date and Time Formulas
2 3 4
Replies
34
Views
3K
awoohaw
awoohaw
K
  • Solved
Help with Weekday Formula
2
Replies
10
Views
902
Fluff
Fluff

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top