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
 
Sort by date Sort by votes
keef2 said:
=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))),"")
Click to expand...
I have checked this formula for all the conditions in it after removing IFERROR, it's working fine
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,834
Messages
6,121,871
Members
449,055
Latest member
excelhelp12345

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