Return dates from calendar table

[Sninge]

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

 

[Sninge]

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?

 

[GR00007]

Well, here's a go - it's kinda kludgy but appears to work.

2/18/2019

1

3

I3:KU3

2/2/2019

3/1/2019

Client

Dte1

Dte2

Dte3

Dte4

Dte5

Dte6

1

Jane

Doe

RowD

RowE

RowF

RowG

RowH

1/12/2019

2/2/2019

3/1/2019

2

Richard

Row

RowD

RowE

RowF

RowG

RowH

8/7/2018

4/19/2019

3

Michal

Sew

RowD

RowE

RowF

RowG

RowH

2/17/2019

3/1/2019

4

Someone

Low

RowD

RowE

RowF

RowG

RowH

12/28/2018

5/6/2019

<tbody>
</tbody>

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.

D1 = Client ID in the example above it is '1'	1
E1 = Row the Client ID is found on	=MATCH(D1,A1:A6,0)
F1 = Calendar array of that row	="I"&E1&":KU"&E1
G1 = date prior to today	=MAXIFS(INDIRECT(F1),INDIRECT(F1),"<"&C1)
H1 = date after today	=MINIFS(INDIRECT(F1),INDIRECT(F1),">"&C1)

<tbody>
</tbody>

 

[Sninge]

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".

18/02/2019

Fri

Mon

Tue

Wed

Thu

Fri

ID

Name

Data

Data

Data

Last Visit

Next Booking

15/02/2019

18/02/2019

19/02/2019

20/02/2019

21/02/2019

22/02/2019

1

Anna

x

x

x

18/02/2019

21/02/2019

09:00

12:00

2

Brian

x

x

x

NEW

19/02/2019

10:00

3

Charlie

x

x

x

15/02/2019

22/02/2019

11:00

15:00

4

Donna

x

x

x

18/02/2019

NO BOOKING

12:00

5

Eva

x

x

x

08:30

15:30

<colgroup><col span="2"><col><col span="2"><col><col><col span="6"></colgroup><tbody>
</tbody>

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

Thanks all

 

[GR00007]

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?

A

B

19/2/2019

D

E

F

G

H

I

J

K

L

M

N

Fri

Mon

Tue

Wed

Thu

Fri

ID

Name

Data

Data

Data

Last Visit

Next Booking

15/2/2019

18/2/2019

19/2/2019

20/2/2019

21/2/2019

22/2/2019

1

Anna

x

x

x

18/2/2019

21/2/2019

8:30

9:00

12:00

2

Brian

x

x

x

NEW

20/2/2019

10:00

3

Charlie

x

x

x

15/2/2019

22/2/2019

11:00

15:00

4

Donna

x

x

x

18/2/2019

NO BOOKING

12:00

5

Eva

x

x

x

15/2/2019

20/2/2019

8:30

15:30

<tbody>
</tbody>

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")

 

[FormR]

Hi, here is another option to try - these are array functions and need to be entered using CTRL+SHIFT+ENTER.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			18/02/2019
2								Fri	Mon	Tue	Wed	Thu	Fri
3	ID	Name	Data	Data	Data	Last Visit	Next Booking	15/02/2019	18/02/2019	19/02/2019	20/02/2019	21/02/2019	22/02/2019
4	1	Anna	x	x	x	18/02/2019	21/02/2019		09:00			12:00
5	2	Brian	x	x	x	NEW	19/02/2019			10:00
6	3	Charlie	x	x	x	15/02/2019	22/02/2019	11:00					15:00
7	4	Donna	x	x	x	18/02/2019	NO BOOKING		12:00
8	5	Eva	x	x	x	15/02/2019	20/02/2019	08:30			15:30
Sheet1

Cell Formulas
Range	Formula
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.

 

[Sninge]

Hi, here is another option to try - these are array functions and need to be entered using CTRL+SHIFT+ENTER.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M
1			18/02/2019
2								Fri	Mon	Tue	Wed	Thu	Fri
3	ID	Name	Data	Data	Data	Last Visit	Next Booking	15/02/2019	18/02/2019	19/02/2019	20/02/2019	21/02/2019	22/02/2019
4	1	Anna	x	x	x	18/02/2019	21/02/2019		09:00			12:00
5	2	Brian	x	x	x	NEW	19/02/2019			10:00
6	3	Charlie	x	x	x	15/02/2019	22/02/2019	11:00					15:00
7	4	Donna	x	x	x	18/02/2019	NO BOOKING		12:00
8	5	Eva	x	x	x	15/02/2019	20/02/2019	08:30			15:30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula 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")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This worked perfectly - thank you so much