Automating episode numbers when date changes

[MariahCab]

I am needing a formula to update the episode number when the dates change in the start of episode and start of next episode columns change.
If there is also a way to automate those to change as the dates occur that would also be GREAT

I appreciate any help!

D2 E2 F2 G2 H2

Start of care	Start of Episode	Episode #	Start of next episode	Next Episode #
1/10/24	3/10/24	6	5/9/24	7
10/3/23	1/1/24	3	3/31/24	4
12/5/23	3/4/24	2	6/2/24	3
10/18/23	1/15/24	2	4/14/24	3
3/19/24	3/19/24	1	6/17/24	2
2/29/24	2/29/24	1	5/29/24	2

 

[Keebler]

The easiest way is

(mrexcel).xlsm
	A	B	C	D	E
16	Start of care	Start of Episode	Episode #	Start of next episode	Next Episode #
17	1/10/2024	3/10/2024	6	5/9/2024	7
18	10/3/2023	1/1/2024	3	3/31/2024	4
19	12/5/2023	3/4/2024	2	6/2/2024	3
20	10/18/2023	1/15/2024	2	4/14/2024	3
21	3/19/2024	3/19/2024	1	6/17/2024	2
22	2/29/2024	2/29/2024	1	5/29/2024	2
23
Sheet1

Cell Formulas
Range	Formula
E17:E23	E17	=IF(D17="","",C17+1)

if you will notice.. simply ading the simple formula to the last column will do what you are asking
I'm almost positive there are other critieria that hasnt been mentioned.. but as requested...

 

[Keebler]

if you are wanting the episode date to advance one based on ANY change
then a vba routine note: (this must be set as a worksheet change)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bRng As Range
Dim bLR As Long

'2
bLR = Cells(Rows.Count, 4).End(xlUp).Row
Set bRng = Range("d2:d" & bLR)

'3
If Not Intersect(Target, bRng) Is Nothing Then
Range("e" & Target.Row).Value = 1 + Range("c" & Target.Row).Value
End If
End Sub

this will auto advance the episode number in column e when the date changes in column d

here is how it works
the first part defines the environment

the second part defines the variables (counts the number of rows to consider, and sets the range)

the third part checks to see if anything changes in column d then advances the count in column e

(mrexcel).xlsm
	A	B	C	D	E
1	Start of care	Start of Episode	Episode #	Start of next episode	Next Episode #
2	1/10/2024	3/10/2024	6	5/9/2024	7
3	10/3/2023	1/1/2024	3	3/31/2024	4
4	12/5/2023	3/4/2024	2	6/2/2024
5	10/18/2023	1/15/2024	2	4/14/2024
6	3/19/2024	3/19/2024	1	6/17/2024
7	2/29/2024	2/29/2024	1	5/29/2024
Sheet2

 

[Keebler]

if you want it to work on both columns b/c and d/e please let me know

 

[Keebler]

the worksheet change can be done
by opening the VBA editor (alt f11)
double clicking the sheet
then selecting Worksheet from the first drop down box(at the top of the vba code window)
and Change in the second drop down box (at the top of the vba code window)
then pasting the vba code provided

 

[MariahCab]

I appreciate your help on this!

 

[Keebler]

You are welcome
Glad I could help

you were the first person I helped