Row ID Order ID Ship Date Order Date Ship Mode Customer ID

40098 CA-2014-AB10015140-41954 11/11/2014 11/13/2014 First Class 3322

26341 IN-2014-JR162107-41675 2/5/2014 2/7/2014 Second Class 332
25330 IN-2014-CR127307-41929 10/17/2014 10/18/2014 First Class
13524 ES-2014-KM1637548-41667 1/28/2014 1/30/2014 First Class
47221 SG-2014-RH9495111-41948 11/6/2014 Same Day
22732 IN-2014-JM156557-41818 6/28/2014 7/1/2014 Second Class
30570 IN-2012-TS2134092-41219 11/6/2012 11/8/2012 First Class
31192 IN-2013-MB1808592-41378 4/14/2013 4/18/2013 Standard Class
40099 CA-2014-AB10015140-41954 11/11/2014 11/13/2014 First Class
36258 CA-2012-AB10015140-40974 3/6/2012 3/7/2012 First Class
36259 CA-2012-AB10015140-40974 3/7/2012 First Class 3
28879 ID-2013-AJ107801-41383 4/19/2013 4/22/2013 First Class 2332
45794 SA-2012-MM7260110-41269 12/26/2012 12/28/2012 Second Class 23
4132 MX-2013-VF2171518-41591 11/13/2013 11/13/2013 Same Day 2
27704 IN-2014-PF1912027-41796 6/6/2014 6/8/2014 Second Class 2332
13779 ES-2015-BP1118545-42216 7/31/2015 8/3/2015 Second Class 32
39519 CA-2012-AB10015140-40958 2/19/2012 2/25/2012 Standard Class 32
12069 ES-2015-PJ1883564-42255 9/8/2015 9/14/2015 Standard Class 2
22096 IN-2015-JS156857-42035 1/31/2015 2/1/2015 First Class 2
49463 TZ-2015-RH9555129-42343 12/7/2015 Second Class 323
46630 PL-2013-AB600103-41494 8/8/2013 8/10/2013 First Class 32
36260 CA-2012-AB10015140-40974 3/6/2012 3/7/2012 First Class
21586 IN-2012-JK1532527-41030 5/1/2012 5/2/2012 First Class 232
13528 ES-2014-LB16795139-41697 2/27/2014 3/1/2014 Second Class 23
1570 US-2015-NP1832582-42216 8/1/2015 First Class 32
3484 MX-2015-VD2167039-42252 9/5/2015 9/8/2015 First Class 23
30191 IN-2012-PB19210127-41259 12/16/2012 12/19/2012 First Class
11645 ES-2012-EB1411048-40981 3/13/2012 3/16/2012 Second Class
38460 CA-2012-AH10030140-41020 4/21/2012 4/23/2012 Second Class
22999 IN-2013-BP1123058-41329 2/24/2013 2/24/2013 Same Day
220 US-2012-RR1952536-41270 12/29/2012 Second Class
10648 ES-2013-BS1136545-41472 7/17/2013 7/19/2013 First Class
40977 CA-2013-AH10030140-41635 12/27/2013 12/31/2013 Standard Class
21286 IN-2012-DP131057-41215 11/2/2012 11/4/2012 Second Class 23
40976 CA-2013-AH10030140-41635 12/27/2013 12/31/2013 Standard Class 23
47905 CG-2012-AH1003033-41165 9/13/2012 9/14/2012 First Class 43
36651 CA-2012-AH10030140-41041 5/12/2012 5/18/2012 Standard Class 232
40100 CA-2012-AH10030140-41273 12/30/2012 12/31/2012 First Class 23
15380 ES-2015-PO18865139-42018 1/14/2015 1/18/2015 Standard Class 23
28046 IN-2012-DL128657-40918 1/11/2012 First Class 23
21316 ID-2014-JB1600059-41873 8/22/2014 8/26/2014 Standard Class 3
29272 IN-2015-BF1100558-42319 11/11/2015 11/15/2015 Standard Class 23
25795 IN-2015-VG2180558-42273 9/26/2015 9/28/2015 Second Class 23
16681 ES-2013-GT14710139-41621 12/13/2013 12/17/2013 Standard Class 23
15953 IT-2012-ZC2191048-41174 9/22/2012 9/24/2012 First Class 23
34608 CA-2014-AH10030140-41719 3/21/2014 3/26/2014 Second Class 23
13847 IT-2014-EB1384045-41706 3/8/2014 3/8/2014 Same Day
24341 IN-2015-AP1091527-42244 8/28/2015 8/29/2015 First Class 3
28701 IN-2015-SW2027558-42125 5/1/2015 5/1/2015 Same Day 3
6550 MX-2015-JH15820141-42356 12/18/2015 12/20/2015 Second Class 323
37425 US-2012-AH10030140-41206 10/27/2012 First Class 23
48360 IR-2015-TG1164060-42265 9/18/2015 9/21/2015 First Class 3
40101 CA-2012-AH10030140-41273 12/30/2012 12/31/2012 First Class 3232
30190 IN-2012-PB19210127-41259 12/16/2012 12/19/2012 First Class 2332
42336 MZ-2014-DG330087-41991 12/18/2014 12/18/2014 Same Day 232
29047 IN-2013-SG2047058-41424 5/30/2013 5/31/2013 First Class
38461 CA-2012-AH10030140-41020 4/21/2012 4/23/2012 Second Class 32
29601 IN-2013-GP1474058-41419 5/25/2013 5/28/2013 Second Class
23499 IN-2015-MW182207-42221 8/5/2015 8/6/2015 First Class
36652 CA-2012-AH10030140-41041 5/12/2012 5/18/2012 Standard Class
26634 IN-2015-CS1246011-42074 3/11/2015 3/13/2015 Second Class 23
34496 CA-2015-AH10030140-42357 12/19/2015 12/23/2015 Standard Class
10522 ES-2015-MS1798048-42227 8/11/2015 8/16/2015 Second Class 2
10549 IT-2014-KC1667548-41882 8/31/2014 9/3/2014 First Class 3
25314 IN-2015-DB1340527-42022 1/18/2015 1/23/2015 Standard Class 2
32637 CA-2015-AS10045140-42007 1/3/2015 1/5/2015 First Class 2
16653 IT-2015-DK13090120-42345 12/7/2015 12/8/2015 First Class 3
28932 IN-2015-CS1184558-42354 12/19/2015 First Class 2
8029 MX-2015-EM1420082-42328 11/20/2015 11/20/2015 Same Day
50411
21191
4960
49085
21209
12161
25438
33130
16988
24443
30199
13879
50788
40834
40425
18241
23013
45616
25026
27962
26669
29600
34336
19195
32639
26731
31278
12035
29149
15896
18307
46257
21142
10308
6776
16948
11611
35876
1873
29120
11743
32648
17522
14721
29629
25868
33131
19559
32638
14070
21306
21668
39465
31650
24260
13646
23708
39466
33661
39467
1779
23880
27094
24424
22049
40796
23815
8009
30267
40795
31647
2283
17765
47783
31649
23175
27693
22134
36297
22488
25678
15376
15162
18899
33660
24160
15812
33662
6449
32472
4394
22058
19833
45807
30187
29484
36347
24015
20601
12051
25915
21639
31648
23229
31646
20674
11331
58
45091
18990
34330
51130
14405
10745
40794
25850
16109
22515
29269
29651
10670
24859
24466
15359
39745
21670
39744
17548
39742
20861
17907
24361
29968
32221
28875
48781
15922
39983
33163
2641
39711
26068
10306
41716
44140
29226
27720
11073
24030
30202
27230
51284
39743
28198
26028
26325
21924
30113
30105
24363
19902
13998
38383
37340
47087
49833
2080
39746
10303
39710
39984
39985
41838
28110
12449
17848
46219
26932
32866
4817
50593
25445
39747
50000
30934
21667
8870
1899
36279
39051
38382
9857
2398
20610
19446
11606
16161
25761
6594
17380
 3503
23537
26037
20266
42956
21995
15570
25831
29383
879
16997
21287
37341
47029
8378
2496
990
34756
40955
33285
43395
36524
46991
9711
31138
46655
10365
21500
28108
16456
Customer Name Segment status name status id status
Consumer Closed not found found
Corporate Closed not found found
Craig Reiter Consumer Closed found not found
Katherine Murray Home Office Closed found not found
Rick Hansen Consumer Open found not found
Jim Mitchum Corporate Closed found not found
sdsds Consumer Closed found not found 1.display the
date contain
Mick Brown Consumer Closed found not found
Aaron Bergman Consumer Closed found not found 2.in name s
column usin
Aaron Bergman Consumer Closed found not found
Consumer Open not found found 3.in id statu
Corporate Closed not found found using isnum
Consumer Closed not found found
Home Office Closed not found found
Consumer Closed not found found
Corporate Closed not found found
Consumer Closed not found found
Patrick Jones Corporate Closed found found
Jim Sink Corporate Closed found found
Ritsa Hightower Consumer Open found found
Ann Blume Corporate Closed found found
Consumer Closed not found not found
Corporate Closed not found found
Home Office Closed not found found
Consumer Open not found found
Consumer Closed not found found
Phillip Breyer Corporate Closed found not found
Eugene Barchas Consumer Closed found not found
Aaron Hawkins Corporate Closed found not found
Benjamin Patterson Consumer Closed found not found
Rick Reed Corporate Open found not found
Bill Shonely Corporate Closed found not found
Aaron Hawkins Corporate Closed found not found
Corporate Closed not found found
Corporate Closed not found found
Corporate Closed not found found
Corporate Closed not found found
Corporate Closed not found found
Consumer Closed not found found
Consumer Open not found found
Consumer Closed not found found
Home Office Closed not found found
Corporate Closed not found found
Consumer Closed not found found
 Consumer Closed not found found
Corporate Closed not found found
Ellis Ballard Corporate Closed found not found
Consumer Closed not found found
Consumer Closed not found found
Consumer Closed not found found
Corporate Open not found found
Consumer Closed not found found
Corporate Closed not found found
Phillip Breyer Corporate Closed found found
Deirdre Greer Corporate Closed found found
Sheri Gordon Consumer Closed found not found
Aaron Hawkins Corporate Closed found found
Guy Phonely Corporate Closed found not found
Mitch Webber Consumer Closed found not found
Aaron Hawkins Corporate Closed found not found
Chuck Sachs Consumer Closed found found
Aaron Hawkins Corporate Closed found not found
Corporate Closed not found found
Corporate Closed not found found
Consumer Closed not found found
Corporate Closed not found found
Consumer Closed not found found
Corporate Open not found found
Evan Minnotte Home Office Closed found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
Open not found not found
 =IF(ISBLANK(C2), "Open", "Closed")
=IF(ISTEXT(G2),"found","not found")
=IF(ISNUMBER(F2),"found","not found")

1.display the status column values as open or closed based on ship date, if ship
date contains some date status column is closed else open =isblank()

2.in name status column display name found or not found based on custmer name
column using =istext()

3.in id status column display found or not found based on customer id column
using isnumber()
 A B C D E F G H I J
1 Text functions
2
3 example for upper cae and lower case
4
5 Name upper case(capital) formula names lower(small letter) formula
6 priya PRIYA =UPPER(A6) TEENA teena =LOWER(D6)
7 anu ANU REENA reena
8 deepa DEEPA POONAM poonam
9 geeta GEETA PINKY pinky
10
11 example for proper case(to make each and every word in capital letter)
12 names proper formula
13 ivY comPutEr educATion Ivy Computer Education =PROPER(A13)
14 jaYanGAr bAngAlOre Jayangar Bangalore
15 example for len function to find the length of the cell including space
16
17 phone len formula
18 23 2 =LEN(TRIM(A18)) 0
19 35345353 8 0(
20 ivy computer education 22 0 0
21 hi 2 =TEXTJOIN(DELIMTER,TRUE/FALSE,CELL1,CELL2,................)
22 hi 2 =LEN(TRIM(A21)) =TEXTJOIN(CHARCTER WHICH SEPARTAES THE VALUE,IGNORES EMPTY
CELL,CEEL1,CELL2,CELL3.............)
23 example for textjoin ( to combine multiple cell content in to single cell)
24
25 names place dept phone sal concatenate FORMULA
26 deepa bang sales 35354534 3434 deepa 35354534 =CONCATENATE(A26," ", D26)
27 reena mysore prod 344 reena
28 priya chennai mang 34234324 2555 priya 34234324
29
30
31
32
33 example replace:
34
35
36 ` replace
37 sal102ban-2020 salChrist2ban-2020 salban-2020 =REPLACE(A37,4,2,"Christ") This function replaces an old text from a string with a new
38 prd104mys-2020 prdChrist4mys-2020 prdmys-2020 string. The input required by this function is the old text,
new text, the starting numbers, and ending numbers of the
39 sal207chn-2020 salChrist7chn-2020 salchn-2020 characters, which need to be replaced. Insert or delete
40 prd308mys-2020 prdChrist8mys-2020 prdmys-2020 between the cell.
41 prd409ban-2020 prdChrist9ban-2020 prdban-2020
42 mng503chn-2020 mngChrist3chn-2020 mngchn-2020
43
44 example for substitute
45
46 idno substitute formula
47 sal102ban sal102del =SUBSTITUTE(A47,"ban","del")
48 prd104mys prd104mys
49 sal107chn sal107chn
50 prd108mys prd108mys
51 prd109ban prd109del
52 mng103chn mng103chn
53
54 example for left , right, mid
55
56 idno Left(dept) Right(place) Mid(idno)
57 sal102ban sal ban 102
58 prd104mys prd mys 104
59 sal107chn sal chn 107
60 prd108mys prd mys 108
 A B C D E F G H I J
61 prd109ban prd ban 109
62 mng103chn mng chn 103
63
64
65
66
67
68 example for find
69 name find
70 computer education institute 10 =FIND("education",A70)
71
72
73
FIND IS CASE SENSITIVE BUT SEARCH IS NOT CASE SENSITIVE
74
75
76 example for search
77
78 name search =SEARCH("education",A79)
79 computer education institute 10
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 A B C D E F G H I J
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
vlook up: vertically looking for a value (column wise searching records in a table) , searching relatrd records
162 searching is done from left to right not in reverse order
163 =vlookup(value aerach , table range, coulumn to extract,0)
164
165
166
167
168
169 to create a table name
select the entire table
170 select formula tab
171 select define name
172 enter the table name
select ok
173
174
175
176
177
178
179
180
 A B C D E F G H I J
181
182
183
184
185
186
187
188
189 horizontaly looking for a value in a table(row wise searching for record in a table)
190 searching takes place from top to down
=hlookup(searching value,table name,row number,0)
191
192
193
194
TOP 10 Films of All Time (Rotten Tomatoes)

Rank Film Title

1 The Wizard of Oz (1939) Directed by King Vidor and Victor Fleming
2 Citizen Kane (1941) Directed by Orson Welles
3 Get Out (2017) Directed by Jordan Peele
4 The Third Man (1949) Directed by Carol Reed
5 Mad Max: Fury Road (2015) Directed by George Miller
6 The Cabinet of Dr. Caligari Das Cabinet des Dr. Caligari (1920) Directed by Robert Wiene
7 All About Eve (1950) Directed by Joseph L. Mankiewicz
8 Inside Out (2015) Directed by Pete Docter and Ronnie del Carmen
9 Metropolis (1927) Directed by Fritz Lang
10 Moonlight (2016) Directed by Barry Jenkins (III)
Studio Year
Warner Bros. (1939)
RKO Radio (1941)
Universal (2017)
Rialto (1949)
Warner Bros. (2015)
Rialto (1920)
20th Century Fox (1950)
Disney/Pixar (2015)
Paramount (1927)
A24 Films (2016)

=MID(B4,FIND("(",B4),6)

Hints
year- find the positin of ) from that position
directed by:find the position of " dirrected b
 Director + Directed by
Directed by King Vidor and Victor Fleming - Warner Bros.
Directed by Orson Welles - RKO Radio
Directed by Jordan Peele - Universal
Directed by Carol Reed - Rialto
Directed by George Miller - Warner Bros.
Directed by Robert Wiene - Rialto
Directed by Joseph L. Mankiewicz - 20th Century Fox
Directed by Pete Docter and Ronnie del Carmen - Disney/Pixar
Directed by Fritz Lang - Paramount
Directed by Barry Jenkins (III) - A24 Films

=CONCATENATE(MID(B4,SEARCH("DIRECTED BY",B4),LEN(B4))," - ",C4)

ositin of ) from that position extract 4 values

d the position of " dirrected by "in a cell and from that position extract all the value till the end
)," - ",C4)
Vehicle regester number Parking time Logout time
KA093456 2:30:20 AM 9:40:00 PM
KA093457 9:30:20 PM 5:30:00 PM
KA093458 11:30:20 AM 11:34:00 PM
KA093459 1:50:10 AM 6:00:33 AM
KA093460 10:00:00 PM 7:00:33 AM
KA093461 9:30:20 AM 8:00:33 AM
KA093462 8:20:20 AM 9:00:33 PM
KA093463 5:30:20 PM 1:30:20 AM
KA093464 1:20:50 AM 11:30:20 PM
KA093465 11:40:20 PM 1:30:20 AM
No of hours parked Parking Charges
19 1900
19 1900
12 600 note:
If c1 (End Time) is earlier than b1 (Start Time), it
4 20 means the time has crossed midnight.We add 1 to
9 360 c1 (since Excel treats 1 as a full day) to account for
22 3300 the next day's time.
Otherwise, it simply calculates c1 - b1 normally.
12 600
8 320
22 3300
1 0

Parking Charges-
more than 2 hours -5RS
more than 5Hours - 40 rs
more than 10 Hours- 50Rs
more than 15 Hours=100 Rs
more than 20 Hours=150 Rs
less than 2Hrs 0=0Rs
rlier than b1 (Start Time), it
crossed midnight.We add 1 to
1 as a full day) to account for

calculates c1 - b1 normally.