Get Cells "Last Modified Date" Using Formula or Function

zero269

Active Member
Joined
Jan 16, 2023
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
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:
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...?
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
ABCDEFGH
1QuizTest DateMarking PeriodTest Get DateMarking PeriodStart DateEnd Date
2553421-Oct1.1 1.02022-07-012022-08-03
36435  1.12022-08-182022-10-21
473393-Aug1.0 1.22022-10-242023-01-13
51030116-Jan1.3 1.32023-01-162023-03-24
64503513-Jan1.2 1.42023-03-272023-06-30
786379read1.325-Feb1.52022-07-012023-06-30
813589724-Oct1.2 Note: 1.5 is a Total range for 1.0 thru 1.4
9150882read1.325-Feb
101663841-Jul1.0 
11172345  
12174424read1.325-Feb
1317812518-Aug1.1 
1418012024-Mar1.3 
15187860read1.325-Feb
LastModified
Cell Formulas
RangeFormula
C2:C15C2=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:D15D2=IF(B2="read",TODAY(),"")



Examples looked at:
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Does anyone know if this is even possible...?
Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,178
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top