How to write dates in excel from first day of year to till date

[Amol_Golhar]

Dear Team,

I have to write a dates in excel from first day of year to till date.

Thanks & Regards,
Amol Golhar

[supriya117 (Supriya Allada)]

Hi @Amol_Golhar

Try this:
Assign activity:

startDate = New DateTime(Now.Year, 1, 1)
endDate = Now.Date

dateList = Enumerable.Range(0, (endDate - startDate).Days + 1).Select(Function(i)startDate.AddDays(i)).ToList()

Write Range activity:
datatable —>dateList.Select(Function(d) New Object() {d}).CopyToDataTable()

or

dateDataTable = (From i In Enumerable.Range(0, (endDate - startDate).Days + 1)
                Select New Object() {startDate.AddDays(i)}
               ).CopyToDataTable()

[lrtetala (Lakshman Reddy)]

Hi @Amol_Golhar

Can you try the below

Code:

Dim dt As DataTable = New DataTable("DateTable")
dt.Columns.Add("Dates", GetType(DateTime))

' LINQ query to generate a list of dates from the first day of the year to the current date
Enumerable.Range(0, (DateTime.Now - New DateTime(DateTime.Now.Year, 1, 1)).Days + 1) _
          .Select(Function(i) New DateTime(DateTime.Now.Year, 1, 1).AddDays(i)) _
          .ToList() _
          .ForEach(Sub(dateItem) dt.Rows.Add(dateItem))

' Assign the generated DataTable to your existing DataTable variable
yourDataTable = dt

Output:

File.xlsx (6.4 KB)

Sequence.xaml (12.3 KB)

Cheers!!

[mkankatala (Mahesh Kankatala)]

Hi @Amol_Golhar

→ Take a assign activity and create a DateTime datatype called currentDate and StartDate.

- Assign -> currentDate = DateTime.Now
- Assign -> StartDate = new DateTime(currentDate.Year, 1, 1)

→ Create a List of DateTime variable called dateRange

- Assign -> dateRange = Enumerable.Range(0, (currentDate - startDate).Days + 1).Select(i >= startDate.AddDays(i)).ToList()

→ Then use the for each activity to iterate the each item in dataRange.
→ Then use excel activities to write the each item in each row in excel.

Hope it helps!!

[rlgandu (Rajyalakshmi Gandu)]

@Amol_Golhar

datesList1=(From i In Enumerable.Range(0, (DateTime.Now - New DateTime(DateTime.Now.Year, 1, 1)).Days + 1)
Select New DateTime(DateTime.Now.Year, 1, 1).AddDays(i)).ToList()

In my process i show you writing the data in a log messages. write the data in excel

[Parvathy (PS Parvathy)]

Hi @Amol_Golhar

startDate= New DateTime(DateTime.Now.Year, 1, 1)
endDate= DateTime.Now
dateList= Enumerable.Range(0, (endDate - startDate).Days + 1).Select(Function(d) startDate.AddDays(d)).ToList()

startDate,endDate is of DataType System.DateTime.
dateList is of DataType System.Collections.Generic.List(System.DaqteTime)

Check the below worflow:
Sequence7.xaml (11.9 KB)

Hope it helps!!

[Krishna_Raj]

@Amol_Golhar
Assign activity:
startDate = New DateTime(DateTime.Now.Year, 1, 1)
endDate = DateTime.Now
counter = 0

Excel Application Scope:
Write Range: “A1”, DataTable with Headers (if using DataTable)

While (startDate <= endDate):
Write Cell or Add DataRow to DataTable
startDate = startDate.AddDays(1)
counter = counter + 1

Close Excel Application Scope

[Anil_G (Anil Gorthi)]

@Amol_Golhar

If you need on excel…

Then you can use the below steps

1. Use excel file and provide the excel path
2. Say you want to start from A1…then use write cell activity with Value as "01/01/2024" and cell as A1
3. Now use one more write cell with A2 as cell and formula as "=EDATE(A1,1)"
4. Now use Auto fill range till end to fill all the dates

Cheers

[Vikas_M]

Hey @Amol_Golhar ,

U can use The below LINQ to get all the dates of current year

Enumerable.Concat(
(From offset In Enumerable.Range(0, (DateTime.Today - New DateTime(DateTime.Today.Year, 1, 1)).Days + 1) 
Select New DateTime(DateTime.Today.Year, 1, 1).AddDays(offset).ToString("dd/MM/yyyy")).ToList,
(
From offset In Enumerable.Range(0, (New DateTime(DateTime.Today.Year + 1, 1, 1) - DateTime.Today).Days)
Select DateTime.Today.AddDays(offset).ToString("dd/MM/yyyy")).ToList
).ToList

I have Replicated your Problem statement in UiPath and below zipped file contains the Workflow

Write_AllDates_ToExcel.zip (11.6 KB)

And below is the output file
Dates.xlsx (10.8 KB)

Hope it helps you out!