How to get the number of hour between 2 date and time

[BharathBabu]

Hello All,

I just wanted to know the formula for the below query.

Start dates:
02/17/2023 09:15:26
02/23/2023 15:15:00
02/23/2023 14:20:25

End date:
02/24/2023 16:10:23
02/23/2023 16:30:02
02/24/2023 10:25:03

Results will be : No of hours taken between start date and end date (sample answer 10:02:30)

Condition:
1. It should not consider weekend dates and non working hours
2. Per day 8 hours working


Appreciate your quick response!

Thanks in advance
Bharath

 

[bosco_yip]

As per the forum rule:

Please forward us a sample file together with your desired result.

And, please see the forum rule:

Site Rules - New Users - Please Read | Chandoo.org Excel Forums - Become Awesome in Excel

Regards

 

[BharathBabu]

Will attach the Excel file

 

[pecoflyer]

One of the many online solutions https://exceljet.net/formulas/get-work-hours-between-dates-and-times

 

[p45cal]

We can do something like this in column C with MS365:


This is a LAMBDA named formula used like:



You get a hint as to what the arguments should refer to, with optional weekend and holiday arguments, whose values should be be as in the NETWORKDAYS.INTL function.
I've guessed a workday starting at 9am and finishing at 5pm, but we need to know this.

I'll await a workbook from the OP.

 

[BharathBabu]

Please find the attached excel.
Highlighted in the colour is the sample output. Expecting final out with hours, minutes, seconds.
I need formula to derive. Excluding Weekends, Per day working hours is 8hours. It should calculate only weekdays

Please do the needful. on quick assistance

 

[pecoflyer]

Please do the needful. on quick assistance

For quick assistance there are pay sites all over the web. Did you even look at the provided suggestions? I think not

 

[BharathBabu]

For quick assistance there are pay sites all over the web. Did you even look at the provided suggestions? I think not

Sorry i dont want to go for pay sites

 

[BharathBabu]

Working Hours is 8hours. Time from 8am to 17pm. Excluding weekends
Same day request completed also be captured

 

[pecoflyer]

Sorry i dont want to go for pay sites

Then don't expect quick AND free answers...
Where did it go wrong when you tried the above suggestions?

 

[BharathBabu]

Then don't expect quick AND free answers...
Where did it go wrong when you tried the above suggestions?

I am not getting correct formula to derive the value.

 

[pecoflyer]

I am not getting correct formula to derive the value.

I can't seem to find any formulas in your sheet

 

[p45cal]

Working Hours is 8hours. Time from 8am to 17pm.

That's 9 hours.