Forum Discussion
Help setting up a formula for the assigned date column from previous dates
Good morning Excel community,
I need help in setting up a complex formula for the assigned date column. I have added an example of two requests in the below sheet.
A requisition number goes through multiple approvals depending on the hierarchy. The submitted date column is the initial date on which the requisition is submitted. Date approved column is the date on which the requisition is approved by the member.
The "Assigned date" column is a manually calculated column but I'm trying to figure out a formula for the column, the Assigned date should be the date on which the requisition is assigned to the specific user.
The real time data will be in an unsorted way, I sorted things in right place for giving a proper example. For the very first approver, the assigned date= Min of the Date approved for a requisition, from next record the Assigned date= Min date approved of the other users within the same requisition. For example for Requisition number-RK260979, the Submit date=02-Oct-2023 and Date approved = 02-Oct-2023. As the requisition is approved on 02-Oct-2023, for the next user the Assigned date should be on 02-Oct-2023, the date approved for next user is 03-Oct-2023 so this will be the assigned date for the next user. Please help me get a proper formula for the issue.
RequisitionNumber SubmitDate DateApproved ApprovedByName Assigned date
RK260979 02-Oct-2023 02-Oct-2023 Funds Commit 02-Oct-2023
RK260979 02-Oct-2023 03-Oct-2023 Kathleen Kennedy 02-Oct-2023
RK260979 02-Oct-2023 04-Oct-2023 Clive Savory 03-Oct-2023
RK260979 02-Oct-2023 23-Oct-2023 William H Joyner Jr 04-Oct-2023
RK260984 02-Oct-2023 02-Oct-2023 Funds Commit 02-Oct-2023
RK260984 02-Oct-2023 05-Oct-2023 Kimberly Martin 02-Oct-2023
RK260984 02-Oct-2023 11-Oct-2023 Clive Savory 05-Oct-2023
RK260984 02-Oct-2023 16-Oct-2023 Brenda Allen 11-Oct-2023
Perhaps in row 2:
=IF(COUNTIF($A$1:$A1,$A2)=0, $C2, MAXIFS($C$1:$C1, $A$1:$A1, $A2))
or
=LET(d, MAXIFS($C$1:$C1, $A$1:$A1, $A2), IF(d=0,$C2, d))
Fill down.
6 Replies
Perhaps in row 2:
=IF(COUNTIF($A$1:$A1,$A2)=0, $C2, MAXIFS($C$1:$C1, $A$1:$A1, $A2))
or
=LET(d, MAXIFS($C$1:$C1, $A$1:$A1, $A2), IF(d=0,$C2, d))
Fill down.
Thank you for the response.
The formula worked well on Row 2. But still I have issues with other rows, as you can see I highlighted a row that is showing as 03-Oct-2023, but in fact it has to be -4-Oct-2023. Also the mark X, a new Requisiton starts from that row and even that is showing up as 03-Oct-2023.
It works for me - see the attached workbook.
