How to generate difference of two dates by reading data from excel and generating the difference dates to new excel file

Reading the Date from, date to and Day of week from attached sample excel file, each date has to be written into a new row when the flight operates. 1234567 in the Day of week refer to the Mon Tue Wed Thu Fri Sat and Sun. If you see a number in that field that means the flight will operate on those days of the week between start and end date. I have attached the sample file.
sample.xlsx (83.0 KB)

Any help is much appreciated.
Thanks in advance

hi @Palaniyappan , Could you please help me in this task.

Hi @Sirimalla_Karthik_Chandra,
Use below code to get the difference between 2 dates based on your input file.
DateTime.ParseExact(row("Date from").ToString(), "dMMMyyy", System.Globalization.CultureInfo.InvariantCulture) - DateTime.ParseExact(row("Date to").ToString(), "ddMMMyy", System.Globalization.CultureInfo.InvariantCulture)

Regards,
Arivu

Hi @Sirimalla_Karthik_Chandra

in Date From column dates are like
Date from
3SEP210
6SEP211
3SEP211
8SEP211
4SEP210
6SEP211
3SEP211
5SEP210
2SEP211
4SEP211
2SEP210

  1. first row has 3Sep210 does that mean 2010?

2.for below 1st row

Date from Date to Day of week
3SEP210 05SEP21 56

56 mean Friday and Saturday so you want to list all the dates that are Friday and Saturday between 3 Sep 2010 and 05 Sep 2021 ?

  1. does start date and end date are inclusive for calculating the the days ?
    for example if start date is 3 Sep 2021 and end date is 5 Sep 2021, what will be number of days 3rd,4th and 5th Sep or 2 days 3rd,4th/4th,5th Sep ?

Hi @AdityaVN , Thanks for replying to my post. Im sorry the date from values got messed up. I’m attaching the revised sample file.

3 sep 2021 was friday and number 5 is equal to friday. likewise

1=monday,
2= tuesday,
3= wednesday ,
4=thursday,
5=friday,
6= saturday,
7= sunday.

From 3 sep 2021 to 5 sep 2021, we have 3 days- friday, saturday and sunday.
so i need to generate dates based on days of week.
I hope you can understand now.
sample.xlsx (82.8 KB)

Hi @arivu96 ,
Thank you for replying to my post. I’ll sure try the code

Hi @Sirimalla_Karthik_Chandra, Can you check this file and tell if it is your desired output?skc.xlsx (90.0 KB)

Hi @vishal.kp , thanks for your efforts. you are almost close to the final output. I’m attaching a image in which you can find the exact output.

Capture

@vishal.kp Could you please share the xaml file.

@Sirimalla_Karthik_Chandra, Is operating date the extra column which shows the output dates?

@vishal.kp, Yes. Taking date from, date to and day of week as inputs. we need to generate all the dates and generate it under operating date column

skc.xaml (16.6 KB)
Try this @Sirimalla_Karthik_Chandra

@vishal.kp, I had run the xaml file. the output is same as you provided in previous excel file. The output generated is not as expected.

@vishal.kp , Dates generated are accurate. But the format of the data should be as shown in the image

Hi @vishal.kp , Thank you so much for your help. Using your code I have figured it out get the desired output.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.