I need to calculate the time elapsed between two dates with time columns.
For E.g, Column A contains 01/02/2023 11:30:25 and Column B contains 01/19/2023 01:20:23, I need to subtract these columns and take out the exact lapsed hours. Answer should come in HOURS
But i have below considerations,
1. Calculations should exclude Weekends, like Saturday and Sunday
2. Calculations should take into account as 19.2 hrs per day and not 24 hrs / day because we work in 2 shifts ( A shift - 7am to 4.45pm & B shift 4.46pm to 2.20am ) From 2.20am to 7.00am we are idle and operation is closed, so should not consider this time in elapsed time.
3. And in the above case example our company had a regional festival holidays on 13,14,15,16,17,18th of Jan'23. So we could able to exclude these days too and since these dates changes dynamically for each case examples, E.g we have our next festival holidays in March month. So if we have given certain days as holidays at once in a beginning it should exclude those days thru our the calculation ( like we do in Networkdays.intl formula in Excel. )
4. And in the consideration (1) there will be a limitation if we exclude weekends completely because in some cases, Start time may be Friday second shift and end time is within the same shift but after 12am, ( Shift closing time is 2.20am ) Means the date will take it as next day, this should also be taken care.
