convert text to date and copy in another column

B

Balkhair

New Member
Joined
Oct 24, 2019
Messages
6
Dear Experts,

I need a formula to convert text to date and copy in column A. Below is the data for reference.

15/09/2019(Sunday)
SnoG0111
SnoG014
SnoG018
SnoG020
SnoG021
SnoG033
SnoG040
SnoG044
SnoG055
SnoG058
SnoG062
SnoG070
16/09/2019(Monday)
SnoG0111
SnoG014
SnoG018
SnoG020
SnoG021
SnoG033
SnoG040
SnoG044
PBU0055
17/09/2019(Tuesday)
SnoG0111
SnoG014
SnoG018
SnoG020
SnoG021
SnoG033
SnoG040
SnoG044
SnoG055
SnoG058
SnoG062
SnoG070
SnoG076
SnoG077
SnoG079
SnoG091
SnoG092
SnoG093
SnoG094
SnoG097
SnoG107
SnoG119
SnoG124
SnoG126
SnoG127
SnoG128
SnoG129
SnoG130
BC003
17/09/2019(Tuesday)
IHD019
PBU0045
PBU0055



<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
The expected output result is something like this.


15/09/201915/09/2019(Sunday)
15/09/2019SnoG0111
15/09/2019SnoG014
15/09/2019SnoG018
15/09/2019SnoG020
15/09/2019SnoG021
15/09/2019SnoG033
15/09/2019SnoG040
15/09/2019SnoG044
15/09/2019SnoG055
15/09/2019SnoG058
15/09/2019SnoG062
15/09/2019SnoG070
16/09/201916/09/2019(Monday)
16/09/2019SnoG0111
16/09/2019SnoG014
16/09/2019SnoG018
16/09/2019SnoG020
16/09/2019
16/09/2019SnoG021
16/09/2019SnoG033
16/09/2019SnoG040
16/09/2019SnoG044
16/09/2019PBU0055
17/09/201917/09/2019(Tuesday)
17/09/2019SnoG0111
17/09/2019SnoG014
17/09/2019SnoG018
17/09/2019SnoG020
17/09/2019SnoG021
17/09/2019SnoG033
17/09/2019SnoG040
17/09/2019SnoG044
17/09/2019SnoG055
17/09/2019SnoG058
17/09/2019SnoG062
SnoG070
17/09/2019SnoG076
17/09/2019SnoG077
17/09/2019SnoG079
17/09/2019SnoG091
17/09/2019SnoG092
17/09/2019SnoG093
17/09/2019SnoG094
17/09/2019SnoG097
17/09/2019SnoG107
17/09/2019SnoG119
17/09/2019SnoG124
17/09/2019SnoG126
17/09/2019SnoG127
17/09/2019SnoG128
17/09/2019SnoG129
17/09/2019SnoG130
17/09/2019BC003

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Using Power Query/Get and Transform here is the Mcode. If unfamiliar with PQ, click on the link in my signature.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each if Text.Contains([Column1.1],"/") then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Column1.1"})
in
    #"Reordered Columns"

Data Range
A​
B​
1​
Date​
Column1.1​
2​
15/09/2019​
15/09/2019​
3​
15/09/2019​
SnoG0111​
4​
15/09/2019​
SnoG014​
5​
15/09/2019​
SnoG018​
6​
15/09/2019​
SnoG020​
7​
15/09/2019​
SnoG021​
8​
15/09/2019​
SnoG033​
9​
15/09/2019​
SnoG040​
10​
15/09/2019​
SnoG044​
11​
15/09/2019​
SnoG055​
12​
15/09/2019​
SnoG058​
13​
15/09/2019​
SnoG062​
14​
15/09/2019​
SnoG070​
15​
16/09/2019​
16/09/2019​
16​
16/09/2019​
SnoG0111​
17​
16/09/2019​
SnoG014​
18​
16/09/2019​
SnoG018​
19​
16/09/2019​
SnoG020​
20​
16/09/2019​
SnoG021​
21​
16/09/2019​
SnoG033​
22​
16/09/2019​
SnoG040​
23​
16/09/2019​
SnoG044​
24​
16/09/2019​
PBU0055​
25​
17/09/2019​
17/09/2019​
26​
17/09/2019​
SnoG0111​
27​
17/09/2019​
SnoG014​
28​
17/09/2019​
SnoG018​
29​
17/09/2019​
SnoG020​
30​
17/09/2019​
SnoG021​
31​
17/09/2019​
SnoG033​
32​
17/09/2019​
SnoG040​
33​
17/09/2019​
SnoG044​
34​
17/09/2019​
SnoG055​
35​
17/09/2019​
SnoG058​
36​
17/09/2019​
SnoG062​
37​
17/09/2019​
SnoG070​
38​
17/09/2019​
SnoG076​
39​
17/09/2019​
SnoG077​
40​
17/09/2019​
SnoG079​
41​
17/09/2019​
SnoG091​
42​
17/09/2019​
SnoG092​
43​
17/09/2019​
SnoG093​
44​
17/09/2019​
SnoG094​
45​
17/09/2019​
SnoG097​
46​
17/09/2019​
SnoG107​
47​
17/09/2019​
SnoG119​
48​
17/09/2019​
SnoG124​
49​
17/09/2019​
SnoG126​
50​
17/09/2019​
SnoG127​
51​
17/09/2019​
SnoG128​
52​
17/09/2019​
SnoG129​
53​
17/09/2019​
SnoG130​
54​
17/09/2019​
BC003​
55​
17/09/2019​
17/09/2019​
56​
17/09/2019​
IHD019​
57​
17/09/2019​
PBU0045​
58​
17/09/2019​
PBU0055​
 
Last edited:
Upvote 0
Is this, copied down, sufficient?

<b>Balkhair</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:152px;" /><col style="width:161px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">15/09/2019(Sunday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG058</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG062</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG070</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">16/09/2019(Monday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">PBU0055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">17/09/2019(Tuesday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG058</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG062</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG070</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG076</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG077</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG079</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG091</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG092</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG093</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >45</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG094</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >46</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG097</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >47</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG107</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >48</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG119</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG124</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >50</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG126</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >51</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG127</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >52</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG128</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >53</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG129</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >54</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG130</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >55</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">BC003</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >56</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >57</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">17/09/2019(Tuesday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >58</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">IHD019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >59</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">PBU0045</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >60</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">PBU0055</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A1</td><td >=IF(B1="","",LEFT<span style=' color:008000; '>(LOOKUP<span style=' color:#0000ff; '>(4,LEFT<span style=' color:#ff0000; '>(B$1:B1,1)</span>+0,B$1:B1)</span>,10)</span>+0)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Hello Sir,

I am new to QP and when i try to run the code after checking the video it gives me an error " Expression.Error: We couldn't find an Excel table named 'Table1'. "


alansidman said:
Using Power Query/Get and Transform here is the Mcode. If unfamiliar with PQ, click on the link in my signature.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each if Text.Contains([Column1.1],"/") then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Column1.1"})
in
    #"Reordered Columns"

Data Range
A​
B​
1​
Date​
Column1.1​
2​
15/09/2019​
15/09/2019​
3​
15/09/2019​
SnoG0111​
4​
15/09/2019​
SnoG014​
5​
15/09/2019​
SnoG018​
6​
15/09/2019​
SnoG020​
7​
15/09/2019​
SnoG021​
8​
15/09/2019​
SnoG033​
9​
15/09/2019​
SnoG040​
10​
15/09/2019​
SnoG044​
11​
15/09/2019​
SnoG055​
12​
15/09/2019​
SnoG058​
13​
15/09/2019​
SnoG062​
14​
15/09/2019​
SnoG070​
15​
16/09/2019​
16/09/2019​
16​
16/09/2019​
SnoG0111​
17​
16/09/2019​
SnoG014​
18​
16/09/2019​
SnoG018​
19​
16/09/2019​
SnoG020​
20​
16/09/2019​
SnoG021​
21​
16/09/2019​
SnoG033​
22​
16/09/2019​
SnoG040​
23​
16/09/2019​
SnoG044​
24​
16/09/2019​
PBU0055​
25​
17/09/2019​
17/09/2019​
26​
17/09/2019​
SnoG0111​
27​
17/09/2019​
SnoG014​
28​
17/09/2019​
SnoG018​
29​
17/09/2019​
SnoG020​
30​
17/09/2019​
SnoG021​
31​
17/09/2019​
SnoG033​
32​
17/09/2019​
SnoG040​
33​
17/09/2019​
SnoG044​
34​
17/09/2019​
SnoG055​
35​
17/09/2019​
SnoG058​
36​
17/09/2019​
SnoG062​
37​
17/09/2019​
SnoG070​
38​
17/09/2019​
SnoG076​
39​
17/09/2019​
SnoG077​
40​
17/09/2019​
SnoG079​
41​
17/09/2019​
SnoG091​
42​
17/09/2019​
SnoG092​
43​
17/09/2019​
SnoG093​
44​
17/09/2019​
SnoG094​
45​
17/09/2019​
SnoG097​
46​
17/09/2019​
SnoG107​
47​
17/09/2019​
SnoG119​
48​
17/09/2019​
SnoG124​
49​
17/09/2019​
SnoG126​
50​
17/09/2019​
SnoG127​
51​
17/09/2019​
SnoG128​
52​
17/09/2019​
SnoG129​
53​
17/09/2019​
SnoG130​
54​
17/09/2019​
BC003​
55​
17/09/2019​
17/09/2019​
56​
17/09/2019​
IHD019​
57​
17/09/2019​
PBU0045​
58​
17/09/2019​
PBU0055​

<tbody>
</tbody>
Click to expand...
 
Upvote 0
Dear Sir,

The above formula is excellent. However, when i copy paste same it gives me an error "#VALUE#"


#VALUE!15/09/2019(Sunday)
#VALUE!AQHG0111
#VALUE!AQHG014
#VALUE!AQHG018
#VALUE!AQHG020
#VALUE!AQHG021
#VALUE!AQHG033
#VALUE!AQHG040
#VALUE!AQHG044
#VALUE!AQHG055
#VALUE!AQHG058
#VALUE!AQHG062

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Your table may have a different name, which means you .will need to change the Mcode to reflect the proper table name. I used Table1
 
Upvote 0
Balkhair said:
The above formula is excellent. However, when i copy paste same it gives me an error "#VALUE#"
Click to expand...
Hard to be sure what the problem is without seeing the workbook. It could be that the data or layout is actually different to what I have as you can see it working in my sheet.

If still unable to resolve, perhaps you could upload a dummy sample file with the problem to a file-share site and provide a shared link to that file?
 
Upvote 0
Another option.
Make sure your data starts in cell B2, put the formula in A2 and copy down.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /><col style="width:192px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">15/09/2019</td><td >15/09/2019(Sunday)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">15/09/2019</td><td >SnoG0111</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">15/09/2019</td><td >SnoG014</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">15/09/2019</td><td >SnoG018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">15/09/2019</td><td >SnoG020</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">15/09/2019</td><td >SnoG021</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">15/09/2019</td><td >SnoG033</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">15/09/2019</td><td >SnoG040</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">15/09/2019</td><td >SnoG044</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">15/09/2019</td><td >SnoG055</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">15/09/2019</td><td >SnoG058</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">15/09/2019</td><td >SnoG062</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">15/09/2019</td><td >SnoG070</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">16/09/2019</td><td >16/09/2019(Monday)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">16/09/2019</td><td >SnoG0111</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">16/09/2019</td><td >SnoG014</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">16/09/2019</td><td >SnoG018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">16/09/2019</td><td >SnoG020</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=IF(B2="","",IF(MID(B2,3,1)="/",LEFT(B2,10)+0,A1))</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Countif formula for dates
Replies
2
Views
431
Oakey
O
B
  • Question
Formula or VBA for Biometrics attendance
Replies
1
Views
589
Balkhair
B
B
  • Question
Copying/Inserting Rows & Data from one sheet to another based on a date lookup
Replies
0
Views
426
Billman101
B
A
  • Question
VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook
Replies
6
Views
672
annuity
A

Forum statistics

Threads
1,215,523
Messages
6,125,318
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top