1

I have to determine per specialty the total percentage of surgeries that started outside the regular working hours or in weekends.

This is what I have so far:

=COUNTIFS(Table1[Start surgery];">17:00")+COUNTIFS(Table1[Start surgery];"<09:00")+COUNTIFS(Table1[Surgery date];"MOD(WEEKDAY(cell), 7) <2")

The first 2 countifs work, but I'm not able to count the weekend days. And a second thing is that its possible for excel to count some surgeries twice, because a surgery can be after 17:00 and in the weekend, but that has to count as one.

Who can help me:)?

Example

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
user3755730
  • 27
  • 1
  • 3

1 Answers1

0

Since the first part of your formula is working for time I just modified the last part of your formula...

=COUNTIFS(Table1[Start surgery];">17:00")+COUNTIFS(Table1[Start surgery];"<09:00")+SUMPRODUCT((WEEKDAY(Table1[Surgery date])=1)+(WEEKDAY(Table1[Surgery date])=7)

Where 1 and 7 are equivalent to Saturday and Sunday.

The current problem with the formula above is that surgery time after 1700 and before 0900 on the weekend are counted twice!

To rectify this all conditions could be moved inside the sumproduct as one potential solution and you would wind up with:

=SUMPRODUCT((WEEKDAY(Table1[Surgery Date])=1)+(WEEKDAY(Table1[Surgery Date])=7)+(WEEKDAY(Table1[Surgery Date])=MEDIAN(WEEKDAY(Table1[Surgery Date]),2,6))*(Table1[Start surgery]<"09:00")+(WEEKDAY(Table1[Surgery Date])=MEDIAN(WEEKDAY(Table1[Surgery Date]),2,6))*(Table1[Start surgery]>"17:00"))

Now the bracket count may be off in there as I was not using excel at the time. The other potential issue we may have is with time. is your time n the time column text or a number formatted to display as time? This may involve modifying are time comparison to a different format, but the formula itself should remain the same.

Basic think here is we put each condition inside the sumproduct. The condition will evaluate to either TRUE or FALSE. If the condition undergoes a math operation it will be turned into 1 for TRUE and 0 for FALSE. + act like OR statements, and * act like AND statement.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • You need to add a check on the times so they do not get double counted on the weekends: `=SUMPRODUCT(((Table1[Start surgery]>time(17,0,0))*(WEEKDAY(Table1[Surgery date],2)<=5))+((Table1[Start surgery] – Scott Craner Jun 05 '16 at 20:57
  • You can use the second criterion on weekday to make Monday = 1 then weekends are days greater than 5. So you need only one check. – Scott Craner Jun 05 '16 at 21:00
  • @ScottCraner I think you beat me to the finish line. Question, was his original fomula with ">17:00" actually working or did he need the ">"&time(17,0.0) there as well. I was wondering if his time was a string or actual excel time. – Forward Ed Jun 05 '16 at 21:08
  • 1
    I dislike using times as strings so I always change it to the formula. It would work as a string in both the sumproduct and the countif. – Scott Craner Jun 05 '16 at 21:11
  • @user3755730 Take a look at Scotts line, it should be working for you. The basic principals are the same with scott's formula as with mine. Scott probably has the syntax down more precise than I do at the moment – Forward Ed Jun 05 '16 at 21:16
  • @user3755730 I fixed the formula brackets and changed the name from medium to median. should work better now – Forward Ed Jun 05 '16 at 21:25
  • 1
    Got it working! Final line: =SUMPRODUCT(((Table1[Start surgery]>TIME(17;0;0))*(WEEKDAY(Table1[Surgery date];2)<=5))+((Table1[Start surgery] – user3755730 Jun 05 '16 at 21:29