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 >1, return (xlookup) value
Here's what I experimented with to achieve all three conditions mentioned above:
A couple things I should note:
Examples looked at:
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).
IF cell is >1, return (xlookup) value
Excel Formula:
IF([@Test Date]>1,XLOOKUP([@Test Date],tblDashboard[Start Date],tblDashboard[Marking Period],,-1),"")
Excel Formula:
=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...?
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