Get Cells "Last Modified Date" Using Formula or Function

[zero269]

Hello,

I'm trying to figure out a way to get a cells "last modified date" in order to use it with XLOOKUP. I've seen examples where people are using formulas to CREATE a TIMESTAMP in a separate column, but I can't use those examples as written considering I have actual dates in the column that will differ from the date they were manually entered. I really just need this for cell values that = "read".

I basically have three conditions that I'm trying to achieve in one formula:

• IF cell is BLANK, do nothing.
• IF cell is >1, return value (xlookup).
• IF cell = "read", get cells 'last modified date', then return value (xlookup).

Here's what I started with:
IF cell is >1, return (xlookup) value

IF([@Test Date]>1,XLOOKUP([@Test Date],tblDashboard[Start Date],tblDashboard[Marking Period],,-1),"")

Here's what I experimented with to achieve all three conditions mentioned above:

=IF(B2="read",XLOOKUP(TODAY(),tblDashboard[Start Date],tblDashboard[Marking Period],,-1),IF(B2=0,"",XLOOKUP(B2,tblDashboard[Start Date],tblDashboard[Marking Period],"",-1)))

A couple things I should note:

• The dates are entered manually as "scheduled" dates which do not reflect the date of entry.
• The "read" entries are often entered on the actual date the book has been confirmed read.
• Using TODAY or NOW for those "read" entries will always return the same date for all entries...?

In my Sample Data, I'm trying to return the Marking Period (C) based on the Test Date (B). I used (D) as a test column to show what Date is being referenced to return the Marking Period for "read" entries. I'm using TODAY() for that test.

VBA Testing.xlsm
	A	B	C	D	E	F	G	H
1	Quiz	Test Date	Marking Period	Test Get Date		Marking Period	Start Date	End Date
2	5534	21-Oct	1.1			1.0	2022-07-01	2022-08-03
3	6435					1.1	2022-08-18	2022-10-21
4	7339	3-Aug	1.0			1.2	2022-10-24	2023-01-13
5	10301	16-Jan	1.3			1.3	2023-01-16	2023-03-24
6	45035	13-Jan	1.2			1.4	2023-03-27	2023-06-30
7	86379	read	1.3	25-Feb		1.5	2022-07-01	2023-06-30
8	135897	24-Oct	1.2			Note: 1.5 is a Total range for 1.0 thru 1.4
9	150882	read	1.3	25-Feb
10	166384	1-Jul	1.0
11	172345
12	174424	read	1.3	25-Feb
13	178125	18-Aug	1.1
14	180120	24-Mar	1.3
15	187860	read	1.3	25-Feb
LastModified

Cell Formulas
Range	Formula
C2:C15	C2	=IF(B2="read",XLOOKUP(TODAY(),tblDashboard[Start Date],tblDashboard[Marking Period],,-1),IF(B2=0,"",XLOOKUP(B2,tblDashboard[Start Date],tblDashboard[Marking Period],"",-1)))
D2:D15	D2	=IF(B2="read",TODAY(),"")

Examples looked at:

Show last date a cell was modified

Hi Guys! I have a table in which the A column indicate the status of a task and the other columns are information regarding that task. I want to have a column (K) that indicates the last time the A cell of the same row as been modified. All data is inputted using a userform, which means the...

www.mrexcel.com

Function for last modified date of cell

First and foremost, thank you to everyone on this forum. I've already found a few answers by just searching. But in this case I'm stumped. So this is what I've done so far (I'm by no means an expert).. this works but breaks in a certain situation. I've got this code in Sheet1 of my...

www.mrexcel.com

Function for last modified date of cell.

Hi, I was looking into this thread, which is pretty close to what I want, but still not quite. I kinda managed to do some modifications and turn it into a function but just don't know enough of these events handles. What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL...

www.mrexcel.com

 

[zero269]

Does anyone know if this is even possible...?
Thanks.