Return dates from calendar table

Sninge

New Member
Joined
Feb 17, 2019
Messages
4
I have a list of client information (columns A-H) and then after that a year's worth of Mon-Fri dates in I3:KU3. Appointment times have been added into the corresponding cell for the client and date. I have cell C1 which calculates the current date. I want to return 2 entries for each client showing the date of last appointment (today or earlier), and date of next appointment (tomorrow onwards) but can't figure it out. Please help
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I have a list of client information (columns A-H) and then after that a year's worth of Mon-Fri dates in I3:KU3. Appointment times have been added into the corresponding cell for the client and date. I have cell C1 which calculates the current date. I want to return 2 entries for each client showing the date of last appointment (today or earlier), and date of next appointment (tomorrow onwards) but can't figure it out. Please help

Anybody?
 
Upvote 0
Well, here's a go - it's kinda kludgy but appears to work.
[TABLE="class: grid, width: 950"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/18/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]I3:KU3[/TD]
[TD="align: right"]2/2/2019[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dte1[/TD]
[TD]Dte2[/TD]
[TD]Dte3[/TD]
[TD]Dte4[/TD]
[TD]Dte5[/TD]
[TD]Dte6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]RowD[/TD]
[TD]RowE[/TD]
[TD]RowF[/TD]
[TD]RowG[/TD]
[TD]RowH[/TD]
[TD][/TD]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]2/2/2019[/TD]
[TD][/TD]
[TD="align: right"]3/1/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Richard[/TD]
[TD]Row[/TD]
[TD]RowD[/TD]
[TD]RowE[/TD]
[TD]RowF[/TD]
[TD]RowG[/TD]
[TD]RowH[/TD]
[TD="align: right"]8/7/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/19/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Michal[/TD]
[TD]Sew[/TD]
[TD]RowD[/TD]
[TD]RowE[/TD]
[TD]RowF[/TD]
[TD]RowG[/TD]
[TD]RowH[/TD]
[TD][/TD]
[TD="align: right"]2/17/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3/1/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Someone[/TD]
[TD]Low[/TD]
[TD]RowD[/TD]
[TD]RowE[/TD]
[TD]RowF[/TD]
[TD]RowG[/TD]
[TD]RowH[/TD]
[TD="align: right"]12/28/2018[/TD]
[TD="align: right"]5/6/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Columns A thru H are client data with a client ID in column A.
Columns I thru KU contain dates disbursed through out.
Cell C1 contains Today().
---------------------------------------------------------------------
Now, added helper cells in D1 thru F1 with the latest date prior to today in G1 and the earliest date after today in H1.
[TABLE="width: 1000"]
<tbody>[TR]
[TD]D1 = Client ID in the example above it is '1'[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E1 = Row the Client ID is found on[/TD]
[TD]=MATCH(D1,A1:A6,0)[/TD]
[/TR]
[TR]
[TD]F1 = Calendar array of that row[/TD]
[TD]="I"&E1&":KU"&E1[/TD]
[/TR]
[TR]
[TD]G1 = date prior to today[/TD]
[TD]=MAXIFS(INDIRECT(F1),INDIRECT(F1),"<"&C1)[/TD]
[/TR]
[TR]
[TD]H1 = date after today[/TD]
[TD]=MINIFS(INDIRECT(F1),INDIRECT(F1),">"&C1)[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi - Thanks for the reply but the data set you have isn't what I have - my fault for posting on my mobile and not including a data set.

Below is my table.

Today's date is in C1 using the =TODAY() formula. The headers and dates are in row 3. I'm trying to return the dates into Last Visit (Column F) and Next Booking (Column G) based on the times that are already in the table. I have put in the first 4 to show what I want to return.

I want Last Booking to return the last date there was a time entry (including today). If there is no entry for today or previous I want to return "NEW".
I want Next Booking to return the next date there is a time entry (from tomorrow onwards). If there is no future booking I want to return "NO BOOKING".


[TABLE="width: 1272"]
<colgroup><col span="2"><col><col span="2"><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Last Visit[/TD]
[TD]Next Booking[/TD]
[TD]15/02/2019[/TD]
[TD]18/02/2019[/TD]
[TD]19/02/2019[/TD]
[TD]20/02/2019[/TD]
[TD]21/02/2019[/TD]
[TD]22/02/2019[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Anna[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]18/02/2019[/TD]
[TD]21/02/2019[/TD]
[TD][/TD]
[TD]09:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]12:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Brian[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]NEW[/TD]
[TD]19/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Charlie[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]15/02/2019[/TD]
[TD]22/02/2019[/TD]
[TD]11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Donna[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]18/02/2019[/TD]
[TD]NO BOOKING[/TD]
[TD][/TD]
[TD]12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Eva[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]08:30[/TD]
[TD][/TD]
[TD][/TD]
[TD]15:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I've racked my brains and hunted all over Google but can't find a solution. Please help!

Thanks all
 
Upvote 0
Got it to work for the sample given, but it is NOT correct - it's using the value of the max/min found instead of the index to get the relative position to grab the date from the header.
Anybody have an idea as to how to resolve that?
[TABLE="class: grid, width: 946"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD="align: right"]19/2/2019[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Last Visit[/TD]
[TD="colspan: 2"]Next Booking[/TD]
[TD="align: right"]15/2/2019[/TD]
[TD="align: right"]18/2/2019[/TD]
[TD="align: right"]19/2/2019[/TD]
[TD="align: right"]20/2/2019[/TD]
[TD="align: right"]21/2/2019[/TD]
[TD="align: right"]22/2/2019[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Anna[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]18/2/2019[/TD]
[TD="align: right"]21/2/2019[/TD]
[TD][/TD]
[TD="align: right"]8:30[/TD]
[TD="align: right"]9:00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Brian[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]NEW[/TD]
[TD="align: right"]20/2/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Charlie[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]15/2/2019[/TD]
[TD="align: right"]22/2/2019[/TD]
[TD][/TD]
[TD="align: right"]11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Donna[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]18/2/2019[/TD]
[TD="colspan: 2"]NO BOOKING[/TD]
[TD][/TD]
[TD="align: right"]12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Eva[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]15/2/2019[/TD]
[TD="align: right"]20/2/2019[/TD]
[TD][/TD]
[TD="align: right"]8:30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Note: This will consistently return the time value for the client's row:
=MAXIFS(I4:KU4,$I$3:$KU$3,"<="&TODAY())
But you need the header date. The below will find the header for the value found, which works for the above, but if you change 8:30 in I4 to 9:00, the date in F2 will change to 15/2/2019.
This was used in F4 and copied down:
=IFERROR(INDEX($I$3:$KU$3,0,MATCH(MAXIFS(I4:KU4,$I$3:$KU$3,"<="&TODAY()),I4:KU4,0)),"NEW")
This is in G4
=IFERROR(INDEX($I$3:$KU$3,0,MATCH(MINIFS(I4:KU4,$I$3:$KU$3,">"&TODAY()),I4:KU4,0)),"NO BOOKING")
 
Upvote 0
Hi, here is another option to try - these are array functions and need to be entered using CTRL+SHIFT+ENTER.


Excel 2013/2016
ABCDEFGHIJKLM
118/02/2019
2FriMonTueWedThuFri
3IDNameDataDataDataLast VisitNext Booking15/02/201918/02/201919/02/201920/02/201921/02/201922/02/2019
41Annaxxx18/02/201921/02/201909:0012:00
52BrianxxxNEW19/02/201910:00
63Charliexxx15/02/201922/02/201911:0015:00
74Donnaxxx18/02/2019NO BOOKING12:00
85Evaxxx15/02/201920/02/201908:3015:30
Sheet1
Cell Formulas
RangeFormula
F4{=IFERROR(LOOKUP(2,IF($H$3:$M$3<=$C$1,IF(ISNUMBER(H4:M4),1)),$H$3:$M$3),"NEW")}
G4{=IFERROR(INDEX($H$3:$M$3,MATCH(1,IF($H$3:$M$3>$C$1,IF(ISNUMBER(H4:M4),1)),0)),"NO BOOKING")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi, here is another option to try - these are array functions and need to be entered using CTRL+SHIFT+ENTER.

Excel 2013/2016
ABCDEFGHIJKLM
FriMonTueWedThuFri
IDNameDataDataDataLast VisitNext Booking
Annaxxx
BrianxxxNEW
Charliexxx
DonnaxxxNO BOOKING
Evaxxx

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]22/02/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]18/02/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]21/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]19/02/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15:00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]18/02/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]15/02/2019[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15:30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]{=IFERROR(LOOKUP(2,IF($H$3:$M$3<=$C$1,IF(ISNUMBER(H4:M4),1)),$H$3:$M$3),"NEW")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]{=IFERROR(INDEX($H$3:$M$3,MATCH(1,IF($H$3:$M$3>$C$1,IF(ISNUMBER(H4:M4),1)),0)),"NO BOOKING")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

This worked perfectly - thank you so much :)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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