Calculate the Financial year for dates

[Amol_Golhar]

Dear Team,

I have a date column (Invoice Date) which contains dates for multiple years.
Please let me know how to calculate the FY for each date and write it in the same row.
Please check the below SS FYR.

Thanks & Regards,
Amol Golhar

[Krishna_Raj]

@Amol_Golhar
CurrentRow(“Calendar Month (Manual)”) = CDate(CurrentRow(“Start”).ToString).AddMonths(9).ToString(“yyyyMM”)
check it
Sample20231117-1L (2).zip (84.5 KB)

[healsko_ho (Healsko)]

Hi @Amol_Golhar
As Krishna’s solution , you may refer to Convert Present Month Year(28/04/2023) To Financial Year(202401) - Help / Studio - UiPath Community Forum.

[AMAN_GUPTA]

You can use formula in the excel itself
CONCAT(TEXT(A1,“YY”),“-”, TEXT((A1),“YY”)+1)

[Gokul_Jayakumar (Gokul Jai)]

Hello @Amol_Golhar
Try this

1. Read Excek file
2. Use If Condition in For each datatale

If condition ---> Cdate(Currentrow("Invoice date")).Month>3

then

Currentrow("Financial Year")= Cdate(Currentrow("Invoice date")).Year

Else

Currentrow("Financial Year")= Cdate(Currentrow("Invoice date")).Year-1

3. Write Range to write the data in excel File.

---

Other wise use Excel Formula. Refer to the Below thread
How to Get the Fiscal Year From a Date in Excel - That Excel Site).

[Parvathy (PS Parvathy)]

Hi @Amol_Golhar

=> Use Read Range Workbook to read the excel and store it in a datatable say dtInput.
Input:

=> Use the below code in Invoke code:

' Create a new DataTable with the same structure as InputDataTable
OutputDataTable = InputDataTable.Clone()

' Iterate through each row in InputDataTable
For Each row As DataRow In InputDataTable.AsEnumerable()
    ' Create a new row in OutputDataTable with the same values
    Dim newRow As DataRow = OutputDataTable.Rows.Add(row.ItemArray)

    ' Calculate Fiscal Year based on Invoice Date
    If row.Field(Of Object)("Invoice Date") IsNot DBNull.Value AndAlso IsDate(row.Field(Of Object)("Invoice Date")) Then
        Dim invoiceDate As DateTime = CDate(row.Field(Of Object)("Invoice Date"))
        Dim fiscalYear As String = If(invoiceDate.Month >= 7, $"{invoiceDate.Year}-{invoiceDate.Year + 1}", $"{invoiceDate.Year - 1}-{invoiceDate.Year}")
        newRow.SetField("Financial Year", fiscalYear)
    Else
        newRow.SetField("Financial Year", DBNull.Value)
    End If
Next

Below are the invoked arguments:

=> Use Write Range Workbook to write the data dtOutput back to excel.
Output:

Workflow:

xaml:
Main.xaml (8.7 KB)

Regards

[mkankatala (Mahesh Kankatala)]

Hi @Amol_Golhar

You can use the Vb code to use in invoke code activity.

For Each row As DataRow In dt.Rows
    ' Assuming "Invoice Date" is the column name for your date column
    Dim invoiceDate As DateTime = DateTime.Parse(row("Invoice Date").ToString())
    
    ' Extract the year from the date
    Dim year As Integer = invoiceDate.Year
    
    ' Determine the financial year based on your business rules
    ' This is just a simple example; adjust it based on your specific requirements
    Dim financialYear As String = If(invoiceDate.Month >= 4, $"{year}-{(year + 1).ToString().Substring(2)}", $"{year - 1}-{year.ToString().Substring(2)}")
    
    ' Write the financial year back to the DataTable
    row("Finanical Year") = financialYear
Next

Workflow -

It’s working for me and change the names of the columns in the code according to your excel.

Hope it helps!!