Split date dd/mm/yy into variables day = dd, month = mm, year = mm

Help
1

I am getting data from a spreadsheet. The cell type in the spreadsheet which contains the date is saved in date format.

Need to loop through the spreadsheet and for each date in format dd/mm/yy I need to get day = dd, month = mm, year =yy

Currently:

ForEach row in DataTable

String S_date = row(“Sdate”).ToString
DateTime S_dt = DateTime.ParseExact(S_date,“dd/MM/yy”, System.Globalization.CultureInfo.InvariantCulture)
Int32 S_day = S_dt.Day
Int32 S_month = S_dt.Month
Int32 S_year = S_dt.Year

However, I get the below error when I try and assign S_dt

image

2

Hi @Aishwarya_Maggi

Can you please print the value row(“Sdate”).ToString and let us know here what exactly you are trying to parse.

The error occurs when the string is not in date format.

Regards,
Karthik

3

When date is 02/03/19 in dd/mm/yy

image

4

Try the below expression

DateTime.ParseExact(S_date,“dd/MM/yy H:mm:ss”, System.Globalization.CultureInfo.InvariantCulture)

5

I’m still getting the same error

6

Can you please try the below format once -

dd/MM/yy hh:mm:ss

If you still getting the error, then the string is not in the format dd/mm/yy as you said.

7

Try this out : Test.xaml (6.0 KB)

8

@Aishwarya_Maggi:

date is defined as String with a value of, for instance, “25/12/19”.

day, month and year is defined as String.
Substring(string-index, string-lenght)

day = date.Substring(0,2) = 25
month = date.Substring(3,2) = 12
year = date.Substring(6,2) = 19

9

Hi @Aishwarya_Maggi,

Try below code
DateTime S_dt= Convert.ToDateTime(S_date)
Int32 S_day = S_dt.Day
Int32 S_month = S_dt.Month
Int32 S_year = S_dt.Year

Regards,
Arivu :slight_smile:

2 Likes
10

This makes ReadRange already read it as a DateTime (unless PreserveFormat is checked).
All of the above solutions are essentially doing:
Get cell value (which is a DateTime) as a string
Parse it to DateTime (with fixed format, aside of Arivu’s answer)
Get elements out

There are unneeded steps there, which are actually dangerous - tostring will output in invariant culture which is month first. It worked here because day was low enough to also function as a month.
Only @arivu96 answer will not break your data, but it still has one line too much (the one quoted). Convert checks the type and if it is the target type it will straight up return it, otherwise will delegate to parse.
If you pass your cell directly to convert call effect will be the same, but without the danger of DateTime → String → DateTime roundtrip parsing.
Do note that all of these will fail if the cell is empty.

11

Hi Dear,
DateTime.ParseExact(S_date,“dd/MM/yy”, System.Globalization.CultureInfo.InvariantCulture) works always in “MM/dd/yyyy” format. Here you are getting wrong format with dd/MM then just behave as a string and split and then concate all three value(day, month and year) as per MM/dd/yyyy.

12

Hello @Aishwarya_Maggi,

You can use substring method

day = myvar.ToString.Substring(0,2)

month = myvar.ToString.Substring(3,2)

year= myvar.ToString.Substring(6,4)