Filling cells with multiple " IF " & " THEN " in the formula

G

Guy Boot

New Member
Joined
Apr 24, 2024
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Rest hour sheet.

I do have W = Work and cell have to be filled in
I do have O/L = Operational leave and cell stays blanc
Conditional I have in W - M = morning = working from 00:00 till 12:00. In the sheet the period from 12:00 to 24:00 will be filled with a " X "
A = afternoon = Working from 12:00 till 24:00. In the sheet the period from 00:00 to 12:00 will be filled with a " X "
D = day = Working from 06:00 till 18:00. In the sheet the period from 00:00 to 06:00 and 18:00 to 24:00 will be filled with a " X "
N = night = Working from 18:00 till 06:00. In the sheet the period from 06:00 to 18:00 will be filled with a " X "
And last but not least, this is for a 12h work day. In case the person does more than 12h te amonth of hours extra needs to be substracted from the " X ". In the sheet down below you have example of row 46 with 4 " X " less which is equal at 2 h. ( Every cross is a half hour )

The worksheet added is an old worksheet whithout the Morning and afternoon and is replaced now, but If I see the formula you suggest, I hope I will manage to fill it in the other form.
1. Crew administration 1.0 Timesheet - Resthours.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACB
37Please mark periods of rest, with an XHours of rest in 24-Hour periodCommentsNot to be completed by the seafarer
38
39
40Hrs0 01 02 03 04 05 06 07 08 09 01 01 11 21 31 41 51 61 71 81 92 02 12 22 3Hours of rest, in any 24-hour periodHours of rest, in any 7-day period
41Date
42
43
44
451xxxxxxxxxxxxxx                        xxxxxxxxxx12
462xxxxxxxxxxxxxx                            xxxxxx10
473xxxxxxxxxxxxxx                        xxxxxxxxxx12
484xxxxxxxxxxxxxx                        xxxxxxxxxx12
495xxxxxxxxxxxxxx                        xxxxxxxxxx12
Rusturen
Cell Formulas
RangeFormula
A45:A49A45=IF(Urenstaat!B21=0,"",Urenstaat!B21)
C45:D49C45=IF($DK36<17.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
E45:F49E45=IF($DK36<18.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
G45:H49G45=IF($DK36<19.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
I45:J49I45=IF($DK36<20.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
K45:L49K45=IF($DK36<21.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
M45:N49M45=IF($DK36<22.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
O45:P49O45=IF($DK36<23.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T2"),"x",""))),"")
Q45:R49Q45=IF($DK36<12.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
S45:T49S45=IF($DK36<13.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
U45:V49U45=IF($DK36<14.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
W45:X49W45=IF($DK36<15.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
Y45:Z49Y45=IF($DK36<16.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AA45:AB49AA45=IF($DK36<17.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AC45:AD49AC45=IF($DK36<18.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AE45:AF49AE45=IF($DK36<19.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AG45:AH49AG45=IF($DK36<20.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AI45:AJ49AI45=IF($DK36<21.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AK45:AL49AK45=IF($DK36<22.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AM45:AN49AM45=IF($DK36<23.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1",$DL36="T2"),"","x"))),"")
AO45:AP49AO45=IF($DK36<12.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1"),"x",""))),"")
AQ45:AR49AQ45=IF($DK36<13.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1"),"x",""))),"")
AS45:AT49AS45=IF($DK36<14.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1"),"x",""))),"")
AU45:AV49AU45=IF($DK36<15.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1"),"x",""))),"")
AW45:AX49AW45=IF($DK36<16.01,IF($DL36="","",(IF(OR($DL36="D",$DL36="T1"),"x",""))),"")
AY45:AY49AY45=COUNTIF(C45:AX45,"x")/2
BD45:BD49BD45=IF(OR($DL36="T1",$DL36="T2"),"Travel",IF(HLOOKUP($DC$5,Urenstaat!$D$10:$FX$66,11+A45)=0," ",HLOOKUP($DC$5,Urenstaat!$D$10:$FX$66,11+A45)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AY45:BC75Cell Valuebetween 0.5 and 9.5textYES



1. Crew administration 1.0 Timesheet - Resthours.xls
DIDJDKDLDMDNDODP
33NaamROBION Guy Ph K
34
35
361W12DW1D
372W14DW2D
383W12DW3D
394W12DW4D
405W12DW5D
416W12DW6D
427W12DW7D
438W12DW8D
449W12DW9D
4510W12DW10D
4611W12DW11D
4712W12DW12D
4813W12DW13D
4914W12DW14D
5015W12DW15D
5116W12DW16D
5217W12DW17D
5318W12DW18D
5419W12DW19D
5520W12DW20D
5621W12DW21D
5722W12DW22D
5823W12DW23D
5924W12DW24D
6025W12DW25D
6126W12DW26D
6227W12DW27D
6328W12DW28D
6429W12DW29D
6530W12DW30D
663100 031 
Rusturen
Cell Formulas
RangeFormula
DJ33DJ33=HLOOKUP($DC$5,Urenstaat!$D$4:$FG$53,2)
DJ36:DJ66DJ36=HLOOKUP($DC$5,Urenstaat!$D$4:$HW$51,$DI36+17)
DK36:DK66DK36=HLOOKUP($DC$5,Urenstaat!$E$20:$HW$51,$DI36+1)
DL36:DL66DL36=IF(HLOOKUP($DC$5,Urenstaat!$E$19:$HW$51,$DI36+2)=0,"",HLOOKUP($DC$5,Urenstaat!$E$19:$HW$51,$DI36+2))
DN36:DN66,DP36:DP66DN36=DJ36
DO36:DO66DO36=DI36
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You must log in or register to reply here.

Similar threads

S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
91
HighAndWilder
H
A
  • Question Question
Macro to Combine Data from Multiple Sheets
Replies
2
Views
345
ashringg
A
L
  • Solved
Excel Formula Multiple if then
2
Replies
12
Views
558
Lisa P NYC
L
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
641
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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