[Solved] Creative Formula Needed: Invoice Processing Time with RPA Bot (Weekends & Custom Workweek)

[Alex_james]

Hi everyone,

I'm building an RPA bot in Excel to automate invoice processing. It picks up invoice creation dates from text files, but I need to calculate the actual processing time, excluding weekends.

Here's the twist: My bot works on Saturdays (unlike the standard Monday-Friday workweek). However, invoices might arrive any day, including Saturdays.

I've tried using DATEDIF to calculate the total time difference, but I'm struggling to exclude weekend hours while considering Saturdays as a working day for my specific workflow.

I've seen some solutions online that involve WEEKDAY functions and custom formulas for weekend exclusion. However, I haven't found one that caters to a non-standard workweek definition (Saturday as working day).

Does anyone have experience with crafting formulas for such scenarios? Any guidance or resources (like sample formulas) would be incredibly helpful!

(P.S. If you're looking to brush up on your Excel date and time manipulation skills, check out Date and Time Calculator

 

[shamimarita]

Hi everyone,

I'm building an RPA bot in Excel to automate invoice processing. It picks up invoice creation dates from text files, but I need to calculate the actual processing time, excluding weekends.

Here's the twist: My bot works on Saturdays (unlike the standard Monday-Friday workweek). However, invoices might arrive any day, including Saturdays.

I've tried using DATEDIF to calculate the total time difference, but I'm struggling to exclude weekend hours while considering Saturdays as a working day for my specific workflow.

I've seen some solutions online that involve WEEKDAY functions and custom formulas for weekend exclusion. However, I haven't found one that caters to a non-standard workweek definition (Saturday as working day).

Does anyone have experience with crafting formulas for such scenarios? Any guidance or resources (like sample formulas) would be incredibly helpful!

(P.S. If you're looking to brush up on your Excel date and time manipulation skills, check out Date and Time Calculator

Hello

I admire that you have taken the initiative to develop such a sophisticated solution, especially with the unique challenge of accommodating a workweek that includes Saturdays.

You can use a combination of Excel functions to calculate the processing time excluding weekends but including Saturdays.
=DATEDIF(start_date, end_date, "D") - INT((end_date - start_date + WEEKDAY(start_date - 1)) / 7)
=DATEDIF(A1, B1, "D") - INT((B1 - A1 + WEEKDAY(A1 - 1)) / 7)

1. This formula assumes that start_date and end_date are valid dates in Excel.
2. The WEEKDAY function adjusts the calculation to account for different starting days of the week.
3. The formula counts Saturdays as working days and excludes only Sundays.

This approach should help you calculate the actual processing time for invoices, considering Saturdays as working days while excluding Sundays.