How to make this IF formula work?

B

Byambadorj

New Member
Joined
Oct 22, 2015
Messages
30
I am relative newbie and cant seem to make this IF formula work.

Mobilisation Forecast (New) - has multiple of steps of onboarding new employees. So, the STATUS cell needs to be able to track each of the stages depending on whether there is a date yyyy-mm-dd.

in example: if both contract is signed and start date has a date < TODAY then it will say "Mobilised" because that person has started his/her work.

In example 2: If the person has completed all the prior stages and currently on "HR System Registered" then the status cell/column would need to say "HR System Registered" etc. and so on.

Mobilisation Forecast (new) doesn't get passed Medical Booked.

Mobilisation Forecast (Old) has more stages, and it tracks each stage flawless.

Mobilisation Forecast New

Mobilisation Forecast - New.xlsx
ABCDEFGHIJKLMNR
3NamePositionManagerStatusJob offer madeContract SignedMedical BookedMedical PassedNew Employee Notification SentIT Request SentHR System RegisteredStart datePoint of HireIT RequirementsDays Took To Mobilize
4Site
5John =IF(AND(ISNUMBER([@[Contract signed]]),[@[Start date]]<=TODAY()),"Mobilised",IF(ISNUMBER([@[Job offer made]]),"Offer Made",IF(ISNUMBER([@[Medical Booked]])>TODAY(),"Medical Booked",IF(ISNUMBER([@[Medical Passed]])<=TODAY()),"Medical Passed",IF(ISNUMBER([@[New Employee Notification Sent]]), "New Employee Notification Sent",IF(ISNUMBER([@[IT Request Sent]]),"IT Request Sent",IF(ISNUMBER([@[HR System Registered]]),"HR System Registered"))))2024-07-302024-07-252024-07-252024-07-252024-07-282024-07-292024-07-29 
6 HR to advise 
7 HR to advise 
8 HR to advise 
9 HR to advise 
10 HR to advise 
11 HR to advise 
12 HR to advise 
13 HR to advise 
14 HR to advise 
15 HR to advise 
16 HR to advise 
17 HR to advise 
18
19Office
20HR to advise 
21HR to advise 
22HR to advise 
23HR to advise 
24HR to advise 
25HR to advise
26HR to advise 
27
Forecast
Cell Formulas
RangeFormula
D6:D17D6=IF(AND(ISNUMBER([@[Contract Signed]]),[@[Start date]]<=TODAY()),"Ажилд орсон",IF(ISNUMBER([@[Medical Booked]]),"Эмнэлэгийн үзлэгт хамрагдсан",IF(ISNUMBER([@[Medical Passed]]),"Тэнцсэн","")))
R5:R17,R26,R20:R24R5=IFERROR(NETWORKDAYS([@[Contract Signed]],#REF!,#REF!),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J5Cell Valuecontains "Approved"textNO
J5Cell Valuecontains "Submitted"textNO
J5Cell Valuecontains "Completed"textNO
D26Cell Value="Medical Booked"textNO
D26Cell Value="NSA Received"textNO
D26Cell Value="Notification Sent"textNO
D26Cell Value="Medical Received"textNO
D26Cell Value="SAP Submitted"textNO
D26Cell Value="Medical Completed"textNO
D26Cell Value="Contract Signed"textNO
D26Cell Value="Contract Not Signed"textNO
A26Cell ValueduplicatestextNO
K5:K26Cell Valuecontains "Approved"textNO
K5:K26Cell Valuecontains "Submitted"textNO
K5:K26Cell Valuecontains "Completed"textNO
A8Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A21Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
A22Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A16Cell ValueduplicatestextNO
A16Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A20Cell ValueduplicatestextNO
A23:A24Cell ValueduplicatestextNO
D5:D24Cell Value="Medical Booked"textNO
D5:D24Cell Value="NSA Received"textNO
D5:D24Cell Value="Notification Sent"textNO
D5:D24Cell Value="Medical Received"textNO
D5:D24Cell Value="SAP Submitted"textNO
D5:D24Cell Value="Medical Completed"textNO
D5:D24Cell Value="Contract Signed"textNO
D5:D24Cell Value="Contract Not Signed"textNO
A18Cell ValueduplicatestextNO
A18Cell ValueduplicatestextNO
Cells with Data Validation
CellAllowCriteria
E5:E17Any value



Mobilisation Forecast - OLD

Mobilisation Forecast - Old.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
3NamePositionManagerPOHSTATUSConfidential Alert ReceivedEmployment Contract SignedEmployment TypePAAF SubmittedPAAF ApprovedNew Starter AlertAccount RequestVisa SubmittedMedical CompletedMedical ReceivedSAP Number Submitted DateSAP Number Received DateSAP Number ProvidedTravel into UB (expats only)Orientation DayTravel to Site/First day in UB OfficeLocationIT Requirement
4OT SITE Personnel 
5Mobilized18-Oct-176-Nov-17National Employee15-Dec-1723-Dec-1727-Dec-17CompletedN/A21-Aug-1721-Aug-1721-Aug-1730-May-1793355969-Jan-18OTLaptop
6SAP Submitted11-Oct-1713-Oct-17International Employee18-Oct-1720-Oct-1716-Nov-17Completed28-Nov-176-Nov-177-Nov-177-Nov-1714-Jan-1815-Jan-1815-Jan-18OTLaptop
7Notification Sent26-Oct-175-Jan-18National Employee4-Jan-184-Jan-18ApprovedN/A8-Jan-188-Jan-188-Jan-18935518818-Jan-18OTLaptop
8Mobilized21-Dec-1721-Dec-17International Employee4-Jan-18Completed22-Dec-173-Jan-185-Jan-185-Jan-186-Jan-18935518815-Jan-1816-Jan-1820-Jan-18OTLaptop
9Mobilized14-Sep-173-Oct-17International Employee11-Oct-1713-Dec-1717-Nov-17Completed3-Oct-1713-Oct-1725-Oct-1725-Oct-1725-Oct-17934948221-Jan-1822-Jan-1823-Jan-18OTLaptop
10Medical Completed20-Dec-1715-Dec-17International Employee4-Jan-184-Jan-18Completed20-Dec-175-Jan-1821-Jan-1822-Jan-1823-Jan-18OTLaptop
11Medical Received24-Nov-1719-Dec-17International Employee4-Jan-185-Jan-1827-Dec-17Completed20-Dec-1710-Jan-1824-Nov-1721-Jan-1822-Jan-1823-Jan-18OTLaptop
12Mobilized24-Nov-1720-Dec-17International EmployeeNot yet requested20-Dec-1724-Nov-1724-Nov-1724-Nov-1727-Nov-17934718621-Jan-1822-Jan-1823-Jan-18OTLaptop
13Medical Completed24-Nov-1719-Dec-17International Employee4-Jan-184-Jan-18Completed20-Dec-178-Jan-1821-Jan-1822-Jan-1823-Jan-18OTLaptop
14Medical Completed16-Nov-178-Jan-18National Employee4-Jan-185-Jan-18ApprovedN/A9-Jan-1826-Jan-18OTLaptop
15Medical Completed7-Nov-178-Jan-18National Employee4-Jan-185-Jan-18ApprovedN/A10-Jan-1826-Jan-18OTLaptop
16Contract Not Signed7-Nov-17HR/Recruitment to adviseNational Employee4-Jan-185-Jan-18N/A26-Jan-18OTLaptop
17Contract Signed24-Nov-1727-Dec-17International Employee4-Jan-184-Jan-18Completed2-Jan-1828-Jan-1829-Jan-1830-Jan-18OTLaptop
18Medical Completed7-Nov-178-Jan-18National Employee4-Jan-185-Jan-18ApprovedN/A9-Jan-182-Feb-18OTLaptop
19Mobilized14-Oct-177-Nov-17International Employee15-Dec-1727-Dec-174-Dec-17Completed7-Nov-1715-Nov-1723-Nov-1723-Nov-1724-Nov-1793520934-Feb-185-Feb-186-Feb-18OTLaptop
20Mobilized31-Oct-178-Nov-17International Employee15-Dec-1721-Nov-17Completed8-Nov-1715-Nov-1723-Nov-1723-Nov-1724-Nov-1793520914-Feb-185-Feb-186-Feb-18OTLaptop
21Mobilized24-Nov-1719-Dec-17International Employee4-Jan-185-Jan-1827-Dec-17Completed20-Dec-1729-Dec-174-Jan-184-Jan-186-Jan-1893551874-Feb-185-Feb-186-Feb-18OTLaptop
22Contract Not Signed24-Nov-17HR/Recruitment to adviseJacobs Expatriate EmployeeNot yet requested4-Feb-185-Feb-186-Feb-18OTLaptop
23Contract Not Signed24-Nov-17HR/Recruitment to adviseInternational EmployeeNot yet requested4-Feb-185-Feb-186-Feb-18OTLaptop
24TOTAL19
25 
26Contract Not Signed12-Dec-17HR/Recruitment to adviseNational EmployeeN/A19-Jan-18UBDesktop
27Mobilized4-Jan-184-Jan-18Jacobs Expatriate EmployeeCompleted3-Jan-1812-Dec-1722-Dec-175-Jan-176-Jan-18935519021-Jan-1822-Jan-1822-Jan-18UBLaptop
28Contract Not Signed21-Dec-17HR/Recruitment to adviseNational EmployeeNot yet requestedN/A29-Jan-18UBLaptop
29Contract Not Signed24-Nov-17HR/Recruitment to adviseInternational EmployeeNot yet requested4-Feb-185-Feb-186-Feb-18UBLaptop
30Medical Completed2-Jan-1829-Dec-17International EmployeeCompleted11-Jan-184-Feb-185-Feb-186-Feb-18UBLaptop
31 TOTAL5
32 
Forecast
Cell Formulas
RangeFormula
E4:E23,E25:E32E4=IF(AND(ISNUMBER([@[SAP Number Received Date]]),[@[Travel to Site/First day in UB Office]]<=TODAY()),"Mobilized",IF(ISNUMBER([@[SAP Number Provided]]),"Notification Sent",IF(ISNUMBER([@[SAP Number Submitted Date]]),"SAP Submitted",IF(ISNUMBER([@[Medical Received]]),"Medical Received",IF(OR([@[Medical Completed]]>TODAY()), "Medical Booked", IF(ISNUMBER([@[Medical Completed]]),"Medical Completed",IF(ISNUMBER([@[New Starter Alert]]),"NSA Received",IF(ISNUMBER([@[Employment Contract Signed]]),"Contract Signed",IF(ISTEXT([@[Employment Contract Signed]]),"Contract Not Signed","")))))))))
U24U24=COUNTIF(U5:U23,">10000")
U31U31=COUNTIF($U$26:$U$30,">10000")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L5:L31Cell Valuecontains "Approved"textNO
L5:L31Cell Valuecontains "Submitted"textNO
L5:L31Cell Valuecontains "Completed"textNO
A8Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A8Cell ValueduplicatestextNO
A27Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
A7Cell ValueduplicatestextNO
A28Cell ValueduplicatestextNO
A20Cell ValueduplicatestextNO
A20Cell ValueduplicatestextNO
A20Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A19Cell ValueduplicatestextNO
A21Cell ValueduplicatestextNO
A21Cell ValueduplicatestextNO
A21Cell ValueduplicatestextNO
A22Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A14Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A13Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A10Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A12Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A9Cell ValueduplicatestextNO
A16Cell ValueduplicatestextNO
A16Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A15Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A6Cell ValueduplicatestextNO
A26Cell ValueduplicatestextNO
A29:A30Cell ValueduplicatestextNO
E5:E30Cell Value="Medical Booked"textNO
E5:E30Cell Value="NSA Received"textNO
E5:E30Cell Value="Notification Sent"textNO
E5:E30Cell Value="Medical Received"textNO
E5:E30Cell Value="SAP Submitted"textNO
E5:E30Cell Value="Medical Completed"textNO
E5:E30Cell Value="Contract Signed"textNO
E5:E30Cell Value="Contract Not Signed"textNO
A24:A25Cell ValueduplicatestextNO
A24:A25Cell ValueduplicatestextNO
A18Cell ValueduplicatestextNO
A18Cell ValueduplicatestextNO
A22:A23Cell ValueduplicatestextNO
A22:A23Cell ValueduplicatestextNO
A23Cell ValueduplicatestextNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to make this IF formale work?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Question
Assistance with finding top 2 repeated values based on 2 criteria (LET,UNIQUE,FILTER,TAKE,SORT,HSTACK,COUNTIF formula)
Replies
3
Views
154
Cubist
Cubist
M
  • Question
Index Match function List based on date
Replies
7
Views
150
Fluff
Fluff
JoshLyman
  • Solved
SUMIFS trouble
Replies
3
Views
376
SanjayGMusafir
SanjayGMusafir
willow1985
  • Solved
Index match formula with multiple matches
2
Replies
14
Views
1K
Alex Blakenburg
A
Jeffrey Mahoney
  • Solved
XMatch Not Returning Expected Result
Replies
4
Views
647
Fluff
Fluff

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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