Home

Excel 2016 : Query Editor : Conditional Column : Help with constructing a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-364364%22%20slang%3D%22en-US%22%3EExcel%202016%20%3A%20Query%20Editor%20%3A%20Conditional%20Column%20%3A%20Help%20with%20constructing%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364364%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20three%20columns%20A%3D%20Date%20of%20Record%20Entry%20%2C%20B%20%3D%20Event%20Date%20(Target%20Date)%20%2C%20C%20%3D%20Days%20Out%20from%20Target%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObjective%20%3A%20Based%20on%20columns%20A%2C%20B%2C%20and%20C%20%3A%20I%20want%20to%20be%20able%20to%20lable%20the%20data%20entry%20date%20in%20column%20A%20that%20match%20with%20the%20target%20date%20in%20column%20B%20as%20%22Final%22%20in%20a%20new%20calculated%20column%20in%20which%20will%20be%20our%20Column%20D.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%20The%20record%20date%20entry%20in%20(%20Column%20A%20)%20are%20manuel%20dates%20that%20have%20entries%20occuring%20past%20the%20target%20date%20in%20(column%20B).%20These%20rows%20are%20represented%20in%20the%20%22Days%20out%22%20(%20column%20C%20)%20as%20negative%20days%20out.%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20negative%20values%20in%20(Column%20C)%20that%20correspond%20to%20a%20target%20date%20listed%20in%20%22Event%20Date%22%20(Column%20B)%3C%2FP%3E%3CP%3ENow%20contain%20the%20record%20or%20row%20of%20information%20we%20would%20like%20to%20label%20as%20the%20final%20date%20in%20the%20calculated%20colum%20D.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%20What%20would%20be%20the%20best%20formula%20to%20construct%20a%20newly%20formed%20calculated%20column%20(column%20D)%20%2C%20that%20would%20seek%20entry%20dates%20(column%20A)%26nbsp%3B%20matching%20the%20target%20dates%20(column%20B)%20unless%20it%20is%20the%20latest%20entry%20date%20entered%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20attempt%3A%20I%20have%20played%20around%20with%20Switch(True()%20and%20If%20functions%2C%20also%20%22%26amp%3B%26amp%3B%22%20and%20%22%7C%7C%22%20but%20unable%20to%20achieve%20my%20intended%20goal%20for%20calculating%20the%20new%20column%20D.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20following%20attached%20workbook%20for%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20any%20contribution%20to%20finding%20a%20solution%20is%20highly%20appreciated%20and%20thank%20you%20for%20any%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Techcommunity%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-364364%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Barak Messina
Occasional Visitor

Hello Excel Community,

 

I have three columns A= Date of Record Entry , B = Event Date (Target Date) , C = Days Out from Target date

 

Objective : Based on columns A, B, and C : I want to be able to lable the data entry date in column A that match with the target date in column B as "Final" in a new calculated column in which will be our Column D. 

 

Problem: The record date entry in ( Column A ) are manuel dates that have entries occuring past the target date in (column B). These rows are represented in the "Days out" ( column C ) as negative days out. 

These negative values in (Column C) that correspond to a target date listed in "Event Date" (Column B)

Now contain the record or row of information we would like to label as the final date in the calculated colum D. 

 

Question: What would be the best formula to construct a newly formed calculated column (column D) , that would seek entry dates (column A)  matching the target dates (column B) unless it is the latest entry date entered ? 

 

My attempt: I have played around with Switch(True() and If functions, also "&&" and "||" but unable to achieve my intended goal for calculating the new column D. 

 

Please find the following attached workbook for reference. 

 

Also, any contribution to finding a solution is highly appreciated and thank you for any assistance.

 

Thank you Techcommunity,

 

B

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies