Extract number from date

Help
1

Hello,

I have an Excel file with the following row:

image
image1149×38 1.16 KB

now i have to split these dates to get the following line:
image

how do i do this? i tried it with the first cell only, since I thought this would be easier but it didn’t work

2

Hi, what would be your goal with this? Do you need to use the dates month number in your process or do you need this to be in your excel file?

3

I wanted to extract the number from the month, and then I wanted to summarize for example every column with the “7” and save the data it in a new column “Jul”
and than the same with “8” and “Aug” and so on

like this:
image

4

Ok, so you need the values in your excel, but do you have only one row in excel with the dates and no header and want it to become two lines? How is your excel when you read it and what do you want it to be after?

5

no there’s no header

it needs to look like this:

Example.xlsx (10.8 KB)

6

but how does it look when it starts? Does it have only row 2? do you know how many dates you have or this can vary?
If this is what you want then look at my sample: Sequence5.xaml (8.7 KB)
You will need to install this:
image

1 Like
7

@Loons Is Sheet1 the Input in that Excel and Sheet2 the Output ? So you would want to Add Columns in Sheet according to the Months in Sheet1 ? Like “Sum Of July”, “Sum Of August” and so on ?

8

yes, this is going to be my next step.

9

@Loons

You can also use split string activity based on seperator as “.” , So you will get array of strings at end , in which second element of array will be month no

Hope it helps

Nived N

10

Theoretical it works. Thank you very much.
But i found a little difference between the example I’ve sent and my original file.
In my original file the date is in “general” data type and not the “Date” data type.
So to use your code, I need to convert the date into the data type “Date” right?
I tried it like this:

image
image808×450 34.1 KB

but i think that’s wrong :confused:

11

@Loons What would be the First Step ? :sweat_smile:

12

Sorry
at first I wanted to write down the number of each month. After that I want to add the columns based on the number/month …like you explained in your first message :slight_smile:

13

@Loons But Why do you need an Extra Step ? :sweat_smile: You can Convert that date to the Format in which you need using DateTime.ParseExact()

14

You need to see how this text date is coming in, you can convert to datetime type like this: DateTime.ParseExact Método (System) | Microsoft Learn