I need help creating a formula that shows Arrival and Departure dates based on data set

[NotaRobot10]

I run into spreadsheets that look like the attached frequently. I am looking for a solution that will provide me with the Arrival date and Departure date without wanting to cry.

The user adds the information by placing a 1 or an x sometimes in the column under the date a guest is staying.

The first column with a value is their arrival date.
The first column with no value is their departure date.

In the case of a value in column C, the departure date would be the following date 4/28.

So for row 2 of the attached, Arrival date is 4/25 and departure date is 4/28.
Row 7 would be Arrival 4/25 and departure 4/26.

Is there a way to make this happen? It can be with formulas or macros or whatever. This is just so time consuming.

Arrival Departure date Example workbook.xlsx

[Glenn Kennedy]

can you add WHAT you expect to see WHERE you expect to see it for two rows, so we can understand exactly what you want.

[AliGW]

Change the headers to real dates and try this:

=MAXIFS($A$1:$C$1,A2:C2,"<>")-MINIFS($A$1:$C$1,A2:C2,"<>")+1

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E
1	25/04/2024	26/04/2024	27/04/2024		Total
2	1	1	1		3
3	1	1	1		3
4	1	1	1		3
5	1	1			2
6	1	1	1		3
7	1				1
8	1	1	1		3
9	1	1	1		3
10	1	1			2
11	1	1			2
12	1	1			2
13	1	1	1		3
14	1	1	1		3
15	1				1
16	1				1
17	1	1	1		3
18	1	1	1		3
19	1	1	1		3
20	1				1

Sheet: Sheet2

[NotaRobot10]

Yes! Sorry, first time posting here.

I'm looking for something like what is on this attachment. Just two new columns, one for arrival date and one for departure date.

Arrival Departure date Example workbook2.xlsx

[AliGW]

Or maybe:

=MINIFS($A$1:$C$1,A2:C2,"<>")

=MAXIFS($A$1:$C$1,A2:C2,"<>")

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G
1	25/04/2024	26/04/2024	27/04/2024		Total	Arrive	Depart
2	1	1	1		3	25/04/2024	27/04/2024
3	1	1	1		3	25/04/2024	27/04/2024
4	1	1	1		3	25/04/2024	27/04/2024
5	1	1			2	25/04/2024	26/04/2024
6	1	1	1		3	25/04/2024	27/04/2024
7	1				1	25/04/2024	25/04/2024
8	1	1	1		3	25/04/2024	27/04/2024
9	1	1	1		3	25/04/2024	27/04/2024
10	1	1			2	25/04/2024	26/04/2024
11	1	1			2	25/04/2024	26/04/2024
12	1	1			2	25/04/2024	26/04/2024
13	1	1	1		3	25/04/2024	27/04/2024
14	1	1	1		3	25/04/2024	27/04/2024
15	1				1	25/04/2024	25/04/2024
16	1				1	25/04/2024	25/04/2024
17	1	1	1		3	25/04/2024	27/04/2024
18	1	1	1		3	25/04/2024	27/04/2024
19	1	1	1		3	25/04/2024	27/04/2024
20	1				1	25/04/2024	25/04/2024

Sheet: Sheet2

[Glenn Kennedy]

It'll probably be:

=MAXIFS($A$1:$C$1,A2:C2,"<>")+1

for departure date.

[HansDouwe]

Please enter real dates in row 1 (no text) and

for arrival date try in F2 and copy down:

Please Login or Register  to view this content.

and for departure date try in G2 and copy down:

Please Login or Register  to view this content.

[NotaRobot10]

You guys are amazing! These worked perfectly!
My team says thank you for saving them so much time! I appreciate the very fast replies!!

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[HansDouwe]

You are Welcome!

Thanks for the feedback and rep. .

Glad to have helped.