Calculating Years, Months, Week, Days, Hours, Minutes for Work Days between two dates

Hello,

I have requested my question HERE where I got the solution but the requirement is change now.

Now I need to calculate Years, Months, Week, Days, Hours, Minutes & Seconds.

For example: I have Receiving Date in A1 as 18-07-2019 8:00:00 AM and response/finishing date in B1 as 25-07-2019 4:30:50 PM. So the result will be in C1 as 01 Weeks, 01 days, 08 hours, 30 minutes and 50 Seconds.

Above example is for only 1 week and 1 day. so possibly we need to have other output as I mentioned above.

I hope my explanation is understandable.

Thanks Paul for responding on my request.

The challenge is formula not to calculate weekends which 2 days in a week starting from Friday and ending Saturday.

Example 1:

Start Date:18-07-2018 08:00:00 AM End Date: 25-07-2019 04:30:50 PM
Actual result should be 01 Weeks, 01 days, 08 hours, 30 minutes and 50 Seconds but your formula giving 1 Weeks, 7 days, 8 Hours, 30 Minutes, 50 Seconds


Example 2:
Start Date:07-07-2019 08:00:00 AM End Date: 25-07-2019 04:10:05 PM
Actual result should be 03 Weeks, 00 days, 08 Hours 30 Minutes and 50 Second but your formula giving 2.57142857142857 Weeks, 18 days, 8 Hours, 10 Minutes, 5 Seconds



The formula must calculate differences with the following.

1. Excluding weekends which is Friday and Saturday.
2. If days exceeding weeks (5 working days) then convert into days means 1 week 2 days if Start date is 14-07-2019 and End Date is 22-07-2019

Dear Paul,

Kindly see the image below.

The 3rd date is having 3 weeks but your formula is calculating 2.57 weeks. Also the 4th Weeks. if the days more than 5 then I assumed as week.

Can you help me on this please.

sss.jpg

Guys,

We need to change in the formula.

Hours required some calculation as per my boss sorry.

the requirement is if the working hours is more than 8 then it will be consider as 1 day. Calculation will be for 8 hours only not for 24 hours.

Can it be possible please.

Hai JeteMc,

As per your request I am attaching a sample file. Sorry to respond as I am preparing sample.

Everything is ok with the formula. The only calculation we need to add here is converting more than 8 hours into days as the shift is for 8 working hours.

222222222.jpg

Hai Bo_Ry,

Appreciate your response on my above request.

It is working as we want. Let me check with my team is this they want and get back to your with feedback. as we are in holidays for a week so I will response to you after next week i.e. 18-08-2019.

Thanks once again.

Sorry for responding late.

Topic marked as solved and reputation add to all users.