Spilt a date range into one row per day

[liorchn]

Hi there,

Can you please help me (I guess with a macro code) how to split a date range into one day per row.

Ive got an excel sheet with 30,000 rows which contains an absence leaves of the company employees (emp number, emp name, absence code, start date, end date).

I want that every row contain only one day and not a date range (I know that this will lead to the addition of many roes but i dont have a choise)

Thank you very much​

 

[alzon21]

Hi please help me how to make VBA for this.

from a daily to consolidated weekly base on the days of operation.

it splits the row because there is no schedule on 21-oct-2015. may be the flight was cancelled.

A	B	C	D	E	F	G	H	I	J	K	L
1	DATA:
2	Carrier IATA code	Flight number	Date	Days of Operation numeric values (MTWTFSS) (1234567)	Departure	Dep Time	Arrival	Arr Time	Seat	Acft code (IATA)	Service Type
9	2XZ	207	2-Oct-15	5	MNL	1025	SYD	2015	230	388	K
10	2XZ	207	5-Oct-15	1	MNL	1025	SYD	2015	230	388	K
11	2XZ	207	7-Oct-15	3	MNL	1025	SYD	2015	230	388	K
12	2XZ	207	9-Oct-15	5	MNL	1025	SYD	2015	230	388	K
13	2XZ	207	12-Oct-15	1	MNL	1025	SYD	2015	230	388	K
14	2XZ	207	14-Oct-15	3	MNL	1025	SYD	2015	230	388	K
15	2XZ	207	16-Oct-15	5	MNL	1025	SYD	2015	230	388	K
16	2XZ	207	19-Oct-15	1	MNL	1025	SYD	2015	230	388	K
17	2XZ	207	23-Oct-15	5	MNL	1025	SYD	2015	230	388	K
18	2XZ	207	26-Oct-15	1	MNL	1025	SYD	2015	230	388	K
19	RESULT
20	Carrier IATA code	Flight number	Effective From DDMMMYY	Effective To DDMMMYY	Days of Operation numeric values (MTWTFSS) (1234567)	Departure	Dep Time	Arrival	Arr Time	Seat	Acft code (IATA)	Service Type
21	2XZ	207	2-Oct-15	19-Oct-15	135	MNL	1025	SYD	2015	230	388	K
22	2XZ	207	23-Oct-15	26-Oct-15	15	MNL	1025	SYD	2015	230	388	K

<colgroup><col width="64" span="13" style="width: 48pt;"></colgroup><tbody>
</tbody>

thanks

Without further guidance/information, I don't see how that could be done logically/consistently. For example, using your sample data for Flight number 207...

- Rows 23-24 below show your expected results.

- Rows 26-27 show a different set of results that seem to me to also fit the same original data.

Also, why does your first 207 result row use a start date of 1-Oct-15 when the first 207 flight is on 2-Oct-15?

Sheet2

*

A

B

C

D

E

F

G

H

I

J

K

L

1

DATA:

*

*

*

*

*

*

*

*

*

*

*

2

Carrier IATA code

Flight number

Date

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

*

9

2XZ

207

2-Oct-15

5

MNL

1025

SYD

2015

230

388

K

*

10

2XZ

207

5-Oct-15

1

MNL

1025

SYD

2015

230

388

K

*

11

2XZ

207

7-Oct-15

3

MNL

1025

SYD

2015

230

388

K

*

12

2XZ

207

9-Oct-15

5

MNL

1025

SYD

2015

230

388

K

*

13

2XZ

207

12-Oct-15

1

MNL

1025

SYD

2015

230

388

K

*

14

2XZ

207

14-Oct-15

3

MNL

1025

SYD

2015

230

388

K

*

15

2XZ

207

16-Oct-15

5

MNL

1025

SYD

2015

230

388

K

*

16

2XZ

207

19-Oct-15

1

MNL

1025

SYD

2015

230

388

K

*

17

2XZ

207

23-Oct-15

5

MNL

1025

SYD

2015

230

388

K

*

18

2XZ

207

26-Oct-15

1

MNL

1025

SYD

2015

230

388

K

*

19

RESULT

*

*

*

*

*

*

*

*

*

*

*

20

Carrier IATA code

Flight number

Effective From DDMMMYY

Effective To DDMMMYY

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

23

2XZ

207

1-Oct-15

14-Oct-15

135

MNL

1025

SYD

2015

230

388

K

24

2XZ

207

16-Oct-15

26-Oct-15

15

MNL

1025

SYD

2015

230

388

K

25

*

*

*

*

*

*

*

*

*

*

*

*

26

2XZ

207

2-Oct-15

19-Oct-15

135

MNL

1025

SYD

2015

230

388

K

27

2XZ

207

23-Oct-15

26-Oct-15

15

MNL

1025

SYD

2015

230

388

K

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:80px;"><col style="width:110px;"><col style="width:170px;"><col style="width:125px;"><col style="width:75px;"><col style="width:57px;"><col style="width:51px;"><col style="width:52px;"><col style="width:67px;"><col style="width:65px;"><col style="width:58px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

I'm sorry, but I am going to be away from the forum for an extended period so won't be able to help further with this anytime in the next few months.

 

[alzon21]

thanks

Hi Peter sorry for the confusion, what i want is a VBA that combines the daily into weekly depends on the date and day of the week.
let me correct the example below.

from the daily row it will combine those flights into single line as shown"row 21" but should be fall on the day of operation such 135.
from the daily data there is no flight on 21oct15 may be it was cancelled so. the single line with stop at 19 oct 15 as it fall on day 1
now it will insert another row (row 22) for the remaining flight which is 23oct that will fall on day 5 and 26oct will fall on day 1
now the multiple daily lines comes with 2 lines which is easy to read.

thanks peter

A	B	C	D	E	F	G	H	I	J	K	L
1	DATA:
2	Carrier IATA code	Flight number	Date	Days of Operation numeric values (MTWTFSS) (1234567)	Departure	Dep Time	Arrival	Arr Time	Seat	Acft code (IATA)	Service Type
9	2XZ	207	2-Oct-15	5	MNL	1025	SYD	2015	230	388	K
10	2XZ	207	5-Oct-15	1	MNL	1025	SYD	2015	230	388	K
11	2XZ	207	7-Oct-15	3	MNL	1025	SYD	2015	230	388	K
12	2XZ	207	9-Oct-15	5	MNL	1025	SYD	2015	230	388	K
13	2XZ	207	12-Oct-15	1	MNL	1025	SYD	2015	230	388	K
14	2XZ	207	14-Oct-15	3	MNL	1025	SYD	2015	230	388	K
15	2XZ	207	16-Oct-15	5	MNL	1025	SYD	2015	230	388	K
16	2XZ	207	19-Oct-15	1	MNL	1025	SYD	2015	230	388	K
17	2XZ	207	23-Oct-15	5	MNL	1025	SYD	2015	230	388	K
18	2XZ	207	26-Oct-15	1	MNL	1025	SYD	2015	230	388	K
19	RESULT
20	Carrier IATA code	Flight number	Effective From DDMMMYY	Effective To DDMMMYY	Days of Operation numeric values (MTWTFSS) (1234567)	Departure	Dep Time	Arrival	Arr Time	Seat	Acft code (IATA)	Service Type
21	2XZ	207	2-Oct-15	19-Oct-15	135	MNL	1025	SYD	2015	230	388	K
22	2XZ	207	23-Oct-15	26-Oct-15	15	MNL	1025	SYD	2015	230	388	K

<tbody>
</tbody>