Filling missing values in Excel

Help
,
21

Hi Dominic,

Need your small help in Excel date time format. Can you please help me in this?

1 Like
22

@aamir, Sure. How may I help you ?

Regards,
Dominic :slight_smile:

1 Like
23

HI Dominic,

i want to remove time part for the transaction date column and that is the only two format of the time.
i am forwarding you an excel sheet with 5 rows actuall DateFormatting.xlsx
there is also some gap before date and time at one row last rowDateFormatting.xlsx (8.1 KB)
to make it more clear i am attaching you one excel sheet.

1 Like
24

@aamir, While accessing the data you can remove the time as follows, [In For each row]

Input: 30/12/2017 15:44:42

Convert.TODateTime(row(“Transaction Date”).ToString.Trim).TOString(“dd/MM/yyyy”)

Output: 30/12/2017

Regards,
Dominic :slight_smile:

1 Like
25

Throwing an error invalid l value

1 Like
26

@aamir, You have to assign it to row(“Transaction Date”).ToString

screenshots please for clear understanding ?

Regards,
Dominic :slight_smile:

1 Like
27

Getting error again
row(“Transaction Date”).ToString = Convert.TODateTime(row(“Transaction Date”).ToString.Trim).TOString(“dd/MM/yyyy”)

image
image1366×768 111 KB

1 Like
28

@aamir,

Edit :

row(“Transaction Date”)= Convert.TODateTime(row(“Transaction Date”).ToString.Trim).TOString(“dd/MM/yyyy”)

Regards,
Dominic :slight_smile:

1 Like
29

Assign : String was not recognized as a valid DateTime.

1 Like
30

@aamir
use this

Convert.TODateTime(row(“Transaction Date”).ToString.Substring(0,10).Trim).TOString(“dd/MM/yyyy”)

Regards,
Mahesh

1 Like
31

after doing that should i assign this to string or where ?

1 Like
32

Hi @aamir,

There is two type of date time you are getting so first you need to identify which format is that then need to convert according that.

you are getting two type of date time
1.“dd/MM/YYYY”
2.“MM/dd/YYYY”

so you need to change to one format

Rergards,
Arivu

1 Like
33

@aamir
You have to Assign that to string.
or You can access directly without assigning also.

Regards,
Mahesh

1 Like
34

Hi arivu,

Need help from you. on the code which you wrote everything is correct but at one or two lines the data is little incorrect.

1 Like
35

Hi Dominic,

Need your help. in the excel which i created there are certain issue at one or two lines.

1 Like
36

can you elaborate @aamir

1 Like
37

Hi arivu,

the excel which i share with you above has some format like this
18098
03/04/2017

04/04/2017

05/04/2017

but the o/p which i am getting is
04-03-2017
03-04-2017
03-04-2017
03-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-04-2017
04-05-2017
05-04-2017
05-04-2017
that means first line order is incorrect and the rest everything is correct

1 Like
38

So whenever there is a change in date at the first line the o/p row has month and day getting changed.
if you want i can share the code which you have sent and i have used here.

1 Like
39

18098 ???

1 Like
40

Let me attach the excel once again for clarity

1 Like