How fetch record within date range with multiple date range in excel

user_3844858 0
Viewed 38

I have two excel sheets, I am trying to find all user details along with request IDs that are accessed on a particular date and time range.

Sheet 1: Request

A B C D E
User Request ID Startdate enddate Business reason
100 1234567 Jul 01, 2022 03:24:11 Jul 01, 2022 06:10:11 SRQ123456
101 1234568 Jul 01, 2022 06:24:11 Jul 01, 2022 08:24:11 CHG123456

Sheet 2: Access details

A B C D
OBJECTNAME ACTION ACCESSBY ACCESSTIME
Business User Update 100 Jul 01, 2022 05:59:12
Workflow Update 100 Jul 01, 2022 06:05:20
Roles Add Workflow Update 100 Jul 01, 2022 06:10:32
SFA Delete 101 Jul 01, 2022 06:24:12

Tried the below code result to find out the record within the date range but I am not able to get the entire row details of sheet1:Request. I have used Name manager from the formula to store the value of Startdate, enddate, and User.

=IF(COUNTIFS(Startdate,"<="&D3,enddate,">="&D3,User,C3)>0,"Yes","No")

Sheet 2: Access details

A B C D E
OBJECTNAME ACTION ACCESSBY ACCESSTIME
Business User Update 100 Jul 01, 2022 05:59:12 Yes
Workflow Update 100 Jul 01, 2022 06:05:20 Yes
Roles Add Workflow Update 100 Jul 01, 2022 06:10:32 No
SFA Delete 101 Jul 01, 2022 06:24:12 Yes

The expected output in Sheet2 should be as below

A B C D E F G H I J
OBJECTNAME ACTION ACCESSBY ACCESSTIME User Request ID Startdate enddate Business reason
Business User Update 100 Jul 01, 2022 05:59:12 Yes 100 1234567 Jul 01, 2022 03:24:11 Jul 01, 2022 06:10:11 SRQ123456
Workflow Update 100 Jul 01, 2022 06:05:20 Yes 100 1234567 Jul 01, 2022 03:24:11 Jul 01, 2022 06:10:11 SRQ123456
Roles Add Workflow Update 100 Jul 01, 2022 06:10:32 No Record not matched
SFA Delete 101 Jul 01, 2022 06:24:12 Yes 100 1234568 Jul 01, 2022 06:24:11 Jul 01, 2022 08:24:11 CHG123456
vlookup excel-formula excel
1 Answers

There are multiple ways to do this.

I will assume that the date values in both, Sheet 1 and Sheet 2, are formatted as dates and not as text. If that is not the case, additional formulas for conversion to date format will be needed.

You could try the following solution:

=OFFSET(Sheet1!$A$1:$E$1,MATCH(1,($C3=Sheet1!$A:$A)*($D3>=Sheet1!$C:$C)*($D3<=Sheet1!$D:$D),0)-1,0)

However, there is no error handling embedded here yet which leads to an error for the 3rd row of data on sheet 2 with the example data that you provided. The date and time of the "access" in the 3rd row is not within the given start and end dates of that "user" on sheet 1 (screenshot for clarification)

Image 1

Therefore, for those 4 rows of data the formula will return the following result:

Image 2

If you want the formula to return something else than the #N/A error, use the =IFERROR(value,value_if_error) function, where 'value' would be the formula above.

user_171587030
Related