Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Date/Time Difference Excluding Weekends & Holidays

I'm running a report to show the turn-around times for a task, but want to
exclude weekends and holidays. For instance if an employee began working on
a task on Friday, March 9th 2007 4:00 pm (column a) and completed it on
Tuesday, March 13th, 2007 4:00 pm (column b), I want to calculate in column c
what the true turn-around time (date/time difference) was, excluding the
Saturday and Sunday in between since these weren't work days and shouldn't
count against that employee. The turnaround time should calculate to 48
hours but because the weekend is in there, it shows as 96 hours. Any ideas?
Thank you!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Date/Time Difference Excluding Weekends & Holidays

A1: start date/time
B1: End date/time

=(NETWORKDAYS(A1,A2,holidays)-1+MOD(A2,1)-MOD(A1,1))*24

Format cell as General


"Paula D" wrote:

I'm running a report to show the turn-around times for a task, but want to
exclude weekends and holidays. For instance if an employee began working on
a task on Friday, March 9th 2007 4:00 pm (column a) and completed it on
Tuesday, March 13th, 2007 4:00 pm (column b), I want to calculate in column c
what the true turn-around time (date/time difference) was, excluding the
Saturday and Sunday in between since these weren't work days and shouldn't
count against that employee. The turnaround time should calculate to 48
hours but because the weekend is in there, it shows as 96 hours. Any ideas?
Thank you!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Date/Time Difference Excluding Weekends & Holidays

Thanks so much Mama - I'll give it a whirl. I appreciate the help!

Paula

"Teethless mama" wrote:

A1: start date/time
B1: End date/time

=(NETWORKDAYS(A1,A2,holidays)-1+MOD(A2,1)-MOD(A1,1))*24

Format cell as General


"Paula D" wrote:

I'm running a report to show the turn-around times for a task, but want to
exclude weekends and holidays. For instance if an employee began working on
a task on Friday, March 9th 2007 4:00 pm (column a) and completed it on
Tuesday, March 13th, 2007 4:00 pm (column b), I want to calculate in column c
what the true turn-around time (date/time difference) was, excluding the
Saturday and Sunday in between since these weren't work days and shouldn't
count against that employee. The turnaround time should calculate to 48
hours but because the weekend is in there, it shows as 96 hours. Any ideas?
Thank you!

  #4   Report Post  
Junior Member
 
Posts: 3
Default

Guys i need help with a similar function in Excel.

I need to substract the time difference to identify the time taken to complete a task, however the following needs to be included

- List of Holidays
- During Business Hours Only

Hence if
A1 = Activity Start Date
B2 = Activity End Date
X1 = Business Hours Start Time
X2 = Business Hours End Time
R1:13 = List of Holidays

How would i get this done ?

I dont want to use the work days formula as it excludes Weekends.

Please help !


Quote:
Originally Posted by Paula D[_2_] View Post
Thanks so much Mama - I'll give it a whirl. I appreciate the help!

Paula

"Teethless mama" wrote:

A1: start date/time
B1: End date/time

=(NETWORKDAYS(A1,A2,holidays)-1+MOD(A2,1)-MOD(A1,1))*24

Format cell as General


"Paula D" wrote:

I'm running a report to show the turn-around times for a task, but want to
exclude weekends and holidays. For instance if an employee began working on
a task on Friday, March 9th 2007 4:00 pm (column a) and completed it on
Tuesday, March 13th, 2007 4:00 pm (column b), I want to calculate in column c
what the true turn-around time (date/time difference) was, excluding the
Saturday and Sunday in between since these weren't work days and shouldn't
count against that employee. The turnaround time should calculate to 48
hours but because the weekend is in there, it shows as 96 hours. Any ideas?
Thank you!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for adding days to a date excluding weekends and holidays? Jake via OfficeKB.com Excel Discussion (Misc queries) 3 May 20th 23 07:48 PM
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 10 February 9th 12 08:34 PM
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 1 February 8th 06 09:56 PM
Workday With Weekends Excluding Holidays Chuy Excel Worksheet Functions 5 January 18th 06 09:04 PM
Formula - Excluding weekends & holidays Connie Martin Excel Worksheet Functions 9 February 25th 05 05:28 AM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"