Days since monitored

A

AndyGav86

New Member
Joined
Nov 17, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I am extremely new with excel, only been using for 3 months. basically, I'm trying to achieve a cell that tells me how many days since they (Officer) were last monitored. I have one sheet that has a list with all the dates they were monitored & a list of what was monitored and the officers name. On the other sheet it has the areas monitored with the officer's name. so, I just want it to say for example if they were monitored on 17/11/2022 and I was to check it two days later the cell would display 2, to indicate 2 days has past since they were monitored.

I really hope that makes sense; I have looked everywhere to find the answer but find it hard to articulate the question.
Many Thanks in advance
 

Attachments

  • Screenshot 2022-11-17 130956.png
    Screenshot 2022-11-17 130956.png
    41.1 KB · Views: 3
  • Screenshot 2022-11-17 131127.png
    Screenshot 2022-11-17 131127.png
    33.8 KB · Views: 3

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
AndyGav86 said:
Hi All,
I am extremely new with excel, only been using for 3 months. basically, I'm trying to achieve a cell that tells me how many days since they (Officer) were last monitored. I have one sheet that has a list with all the dates they were monitored & a list of what was monitored and the officers name. On the other sheet it has the areas monitored with the officer's name. so, I just want it to say for example if they were monitored on 17/11/2022 and I was to check it two days later the cell would display 2, to indicate 2 days has past since they were monitored.

I really hope that makes sense; I have looked everywhere to find the answer but find it hard to articulate the question.
Many Thanks in advance
Click to expand...
To answer this question there's a need to know the worksheets names and to see the columns in the screenshot
 
Upvote 0
20731461 & 20733280.XLS.xlsx
ABCD
1DateNameAreaPass/Fail
217/11/2022Jimloading
315/11/2022joebody
416/11/2022Saladloading
510/10/2022Lettucex-ray
605/08/2022Jimx-ray
717/11/2022Joex-ray
810/10/2022Saladx-ray
917/11/2022Lettuceloading
1004/05/2022Lettucebody
1101/01/2022Jimbag
1202/05/2022Joebag
1306/08/2022Saladbag
Sheet2


20731461 & 20733280.XLS.xlsx
ABCDEF
3nameteamLoadingX-Raybagbody
4JimA 0104320-
5JoeB-01992
6SaladC138103-
7LettuceD038-197
Sheet1
Cell Formulas
RangeFormula
C4:F4C4=IFERROR(TODAY()-XLOOKUP(C$3&$A$4,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C5:F5C5=IFERROR(TODAY()-XLOOKUP(C$3&$A$5,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C6:F6C6=IFERROR(TODAY()-XLOOKUP(C$3&$A$6,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C7:F7C7=IFERROR(TODAY()-XLOOKUP(C$3&$A$7,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
 
Upvote 0
Kerryx said:
20731461 & 20733280.XLS.xlsx
ABCD
1DateNameAreaPass/Fail
217/11/2022Jimloading
315/11/2022joebody
416/11/2022Saladloading
510/10/2022Lettucex-ray
605/08/2022Jimx-ray
717/11/2022Joex-ray
810/10/2022Saladx-ray
917/11/2022Lettuceloading
1004/05/2022Lettucebody
1101/01/2022Jimbag
1202/05/2022Joebag
1306/08/2022Saladbag
Sheet2


20731461 & 20733280.XLS.xlsx
ABCDEF
3nameteamLoadingX-Raybagbody
4JimA 0104320-
5JoeB-01992
6SaladC138103-
7LettuceD038-197
Sheet1
Cell Formulas
RangeFormula
C4:F4C4=IFERROR(TODAY()-XLOOKUP(C$3&$A$4,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C5:F5C5=IFERROR(TODAY()-XLOOKUP(C$3&$A$5,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C6:F6C6=IFERROR(TODAY()-XLOOKUP(C$3&$A$6,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C7:F7C7=IFERROR(TODAY()-XLOOKUP(C$3&$A$7,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
Click to expand...
Kerryx said:
20731461 & 20733280.XLS.xlsx
ABCD
1DateNameAreaPass/Fail
217/11/2022Jimloading
315/11/2022joebody
416/11/2022Saladloading
510/10/2022Lettucex-ray
605/08/2022Jimx-ray
717/11/2022Joex-ray
810/10/2022Saladx-ray
917/11/2022Lettuceloading
1004/05/2022Lettucebody
1101/01/2022Jimbag
1202/05/2022Joebag
1306/08/2022Saladbag
Sheet2


20731461 & 20733280.XLS.xlsx
ABCDEF
3nameteamLoadingX-Raybagbody
4JimA 0104320-
5JoeB-01992
6SaladC138103-
7LettuceD038-197
Sheet1
Cell Formulas
RangeFormula
C4:F4C4=IFERROR(TODAY()-XLOOKUP(C$3&$A$4,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C5:F5C5=IFERROR(TODAY()-XLOOKUP(C$3&$A$5,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C6:F6C6=IFERROR(TODAY()-XLOOKUP(C$3&$A$6,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
C7:F7C7=IFERROR(TODAY()-XLOOKUP(C$3&$A$7,Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13,"",0,1),"-")
Click to expand...
Thank you so so much that has done it :)
Without sounding ungrateful is there any way that say if "Jim" was monitored on loading on the 01/11/22 then again on the 15/11/22 could the cell then show the most recent time between? at the moment it shows the first date that was recorded in relation to todays date, if that makes sense?
 
Upvote 0
Not pretty but seems to do the job
Book1
ABCDEF
3nameteamLoadingX-Raybagbody
4JimA 11105321--
5JoeB--172001
6SaladC239104--
7LettuceD--39----
Sheet1
Cell Formulas
RangeFormula
C4:C7C4=IF(TODAY()-MAX(IF($C$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($C$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
D4:D7D4=IF(TODAY()-MAX(IF($D$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($D$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
E4:E7E4=IF(TODAY()-MAX(IF($E$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($E$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
F4:F7F4=IF(TODAY()-MAX(IF($F$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($F$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
 
Upvote 0
Solution
Kerryx said:
Not pretty but seems to do the job
Book1
ABCDEF
3nameteamLoadingX-Raybagbody
4JimA 11105321--
5JoeB--172001
6SaladC239104--
7LettuceD--39----
Sheet1
Cell Formulas
RangeFormula
C4:C7C4=IF(TODAY()-MAX(IF($C$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($C$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
D4:D7D4=IF(TODAY()-MAX(IF($D$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($D$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
E4:E7E4=IF(TODAY()-MAX(IF($E$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($E$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
F4:F7F4=IF(TODAY()-MAX(IF($F$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13))<>TODAY(),TODAY()-MAX(IF($F$3&$A4=Sheet2!$C$2:$C$13&Sheet2!$B$2:$B$13,Sheet2!$A$2:$A$13)),"--")
Click to expand...
YOU ARE A GENIUS!!! Thank you so much for all your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
144
ExcelenteNovel
E
MPW
  • Solved
How can I determine the dpi zoom settings for the primary monitor?
Replies
6
Views
840
MPW
MPW
O
  • Question
Days, Employees, Projects, Sums
Replies
1
Views
423
jorismoerings
J
P
  • Question
Summing Data based on a count of prior month data
2
Replies
12
Views
642
KRice
K
N
  • Question
VBA - Copying specific data to open workbook.
Replies
1
Views
312
eiloken
E

Forum statistics

Threads
1,215,030
Messages
6,122,762
Members
449,095
Latest member
m_smith_solihull

Share this page

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