Thread: WORK HOURS DIFFERENCE BETWEEN TWO DATES
View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
plinius plinius is offline
external usenet poster
  
Posts: 51
Default WORK HOURS DIFFERENCE BETWEEN TWO DATES
Il 07/12/2012 12:24, ha scritto:
This formula calculates te total work hours and minutes between two dates.
I use it to calculate how long it takes for someone to answer my mails at work.
One workday is 10 hours in this formula. You can change this.

B3(mail send), format as d/mm/yyyy h:mm , 23/02/2012 09:00
G3(reply received), format as d/mm/yyyy h:mm , 28/02/2012 17:00

H3(hours and minutes, format as h:mm):

=((((((NETWORKDAYS(B3;G3))-1)*10)+(IF(HOUR(B3)<HOUR(G3);HOUR(G3)-HOUR(B3);-(HOUR(B3)-HOUR(G3)))))*60)+(IF(MINUTE(B3)<MINUTE(G3);MINUTE( G3)-MINUTE(B3);(60-(MINUTE(B3)-MINUTE(G3)))-60)))/1440


slim mode:
=(NETWORKDAYS(B3,G3)-1)*10/24+(MOD(G3,1)-MOD(B3,1))

Hi,
E.
Reply With QuoteReply With Quote