how to calculate between times

N

noelf

hello all

can someone pls advice.
i need to calculate the time between start to finish dates.
A1
08/07/05 12:00

A2
09/07/05 1200

i found this formula =text(a2-a1,"h:mm") to be helpful.
but i want to calculate business hours only which is from 9am to 5:30.
can someone pls advice what formula i can use?
thanks
 
A

Alex Delamain

Try this

=17.5/24-(A1-ROUNDDOWN(A1,0)) gives number of hours worked on 1st day

=NETWORKDAYS(A1,A2) gives number of full (8 1/2 hour) working days
(multiply by 8.5/24 to get hours)

=A2-(ROUNDDOWN(A2,0)+0.375) gives number of hours worked on the final
day

add them all up and you should have the number of days/ hours worked
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number of hours between now and selected date 5
Calculating interest due on late loan payments 1
How to calculate Premium Time 2
Expanding Date formula 1
Time calculation - Critical 12
Calculate elapsed time excluding specific timeframe 10
date range 11
How do i Calculate minutes between two different dates 5

Top