Count hours on specific days and time, return value in decimal hours

W

Want2BExcel

Board Regular
Joined
Nov 24, 2021
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
I want to count the hours from Saturday 14:00 to Mondag 06:00 and return value in decimal hours. But should ignore ALL if [Tidsart1] column E's value is "volunteer" (this last part I haven't included in any formula yet.
I'm trying to simplify the formula in the TEST (AK) column or combine thoose I've got (when errors is corrected). Right now it's a bunch of different formulas. Some are working, some has errors (Marked with red in the table)

There's 3x2 time colums. C+D (start1-stop1), F+G (start2-stop2), I+J (start3-stop3). There isn't always has data in all 3. If C+D don't, then the rest will not either, in that case, the result should alwasy be 0

[Tuesdag to Friday]=AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4 - This formula is working

[Monday]=AK7,AK28,AK21,AK14 - This formula seems to work somewhat ok if the result is positive, but I want it to return a value of 0 if the result is negative

[Saturday]=AK5,AK26,AK19,AK12 - This formula's result confuse me. In AK5 it looks to be working fine, only counting the hours from 14:00-15:00, but in AK12 it is not working, since it's counting all hours from F+G (12:45-15:00), I+J (19:00-00:00), but correctly ignores C+D (00:00-01:00). So it looks like I have an issue when there's data in other time columns than C+D

[Sunday]=AK6,AK27,AK20,AK13 - This formula is working, but seems a bit comprehensive when all hours should be counted


Arb.tid & Friheder TEST.xlsx
ABCDEFGHIJKAK
1UgedagDatoStart1Slut1Tidsart1Start2Slut2Tidsart2Start3Slut3Tidsart3TEST
2Onsdag01-12-2107:3015:30FTG0
3Torsdag02-12-2107:3014:30FTG0
4Fredag03-12-2107:3013:30FTG0
5Lørdag04-12-2112:4515:00T1
6Søndag05-12-2100:0001:00T12:4515:00T19:0000:00T8,25
7Mandag06-12-2106:4508:30R-0,75
8Tirsdag07-12-2100:0001:00T14:4500:00T0
9Onsdag08-12-2100:0001:00T14:4500:00T0
10Torsdag09-12-2100:0001:00T12:4500:00T0
11Fredag10-12-2100:0001:00T14:4500:00T0
12Lørdag11-12-2100:0001:00T12:4515:00T19:0000:00T7,25
13Søndag12-12-2117:4520:15R2,5
14Mandag13-12-2100:0008:30R6
15Tirsdag14-12-2107:3015:30FTG0
16Onsdag15-12-2107:3015:30FTG0
17Torsdag16-12-2107:3014:30FTG0
18Fredag17-12-2107:3013:30§170
19Lørdag18-12-210
20Søndag19-12-210
21Mandag20-12-2108:0017:00T-2
22Tirsdag21-12-2107:3015:30T0
23Onsdag22-12-2107:3015:30T0
24Torsdag23-12-2107:3014:30T0
25Fredag24-12-2107:3013:30T0
26Lørdag25-12-2107:3015:30volunteer1,5
27Søndag26-12-2107:3015:30volunteer8
28Mandag27-12-2105:3015:30volunteer0,5
29Tirsdag28-12-2107:3015:30T0
30Onsdag29-12-2107:3015:30T0
31Torsdag30-12-2107:3014:30T0
32Fredag31-12-2107:3013:30T0
Dec.21
Cell Formulas
RangeFormula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4AK2=(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AK5,AK26,AK19,AK12AK5=24*((IF(OR([@Ugedag]="Lørdag",[@Start1]<TIME(14,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(14,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(14,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(14,0,0),0))))+(IF(OR([@Start2]>TIME(14,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(14,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(14,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(14,0,0),0))))+(IF(OR([@Start3]>TIME(14,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(14,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(14,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(14,0,0),0)))))
AK6,AK27,AK20,AK13AK6=24*((IF(OR([@Ugedag]="Søndag",[@Start1]<TIME(0,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(0,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(0,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(0,0,0),0))))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(0,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(0,0,0),0))))+(IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(0,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(0,0,0),0)))))
AK7,AK28,AK21,AK14AK7=24*((IF(OR([@Ugedag]="Mandag",[@Start1]>TIME(0,0,0),[@Start1]="",[@Slut1]=""),MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(6,0,0))-MAX([@Start1],0,0)),0))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],6,0),0))+IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],6,0),0))))
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
this is already a start for the volunteers and the tuesday till friday.
What do you try to do with the formula, all the hours after 14:00 ?
Map1
AKAL
1TESTKolom1
20,000
30,000
40,000
51,00formula
68,25formula
7-0,75formula
80,000
90,000
100,000
110,000
127,25formula
132,50formula
146,00formula
150,000
160,000
170,000
180,000
190,00formula
200,00formula
21-2,00formula
220,000
230,000
240,000
250,000
261,500
278,000
280,500
290,000
300,000
310,000
320,000
Blad2
Cell Formulas
RangeFormula
AK2:AK4,AK8:AK11,AK15:AK18,AK22:AK25,AK29:AK32AK2=(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AL2:AL32AL2=IF(OR([@Tidsart1]="volunteer",WEEKDAY([@Dato],12)<=4),0,"formula")
AK5,AK12,AK19,AK26AK5=24*((IF(OR([@Ugedag]="Lørdag",[@Start1]<TIME(14,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(14,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(14,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(14,0,0),0))))+(IF(OR([@Start2]>TIME(14,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(14,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(14,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(14,0,0),0))))+(IF(OR([@Start3]>TIME(14,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(14,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(14,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(14,0,0),0)))))
AK6,AK13,AK20,AK27AK6=24*((IF(OR([@Ugedag]="Søndag",[@Start1]<TIME(0,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(0,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(0,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(0,0,0),0))))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(0,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(0,0,0),0))))+(IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(0,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(0,0,0),0)))))
AK7,AK14,AK21,AK28AK7=24*((IF(OR([@Ugedag]="Mandag",[@Start1]>TIME(0,0,0),[@Start1]="",[@Slut1]=""),MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(6,0,0))-MAX([@Start1],0,0)),0))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],6,0),0))+IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],6,0),0))))
 
Upvote 0
I corrected your formulas; and I will have a look on a way to shortening these down. And also to implement the volunteers criteria.
And it would have been nice, if the XL2BB tools did work to translate language formulas back to regional language, as "BSALV" also pointed out ;-)

arbtider.xlsx
ABCDEFGHIJKLAJAK
1UgedagDatoStart1Slut1Tidsart1Start2Slut2Tidsart2Start3Slut3Tidsart3TEST
2Onsdag01-12-202107:3015:30FTG0
3Torsdag02-12-202107:3014:30FTG0
4Fredag03-12-202107:3013:30FTG0
5Lørdag04-12-202112:4515:00T2,25
6Søndag05-12-202100:0001:00T12:4515:00T19:000T8,25
7Mandag06-12-202106:4508:30R0
8Tirsdag07-12-202100:0001:00T14:4500:00T0
9Onsdag08-12-202100:0001:00T14:4500:00T0
10Torsdag09-12-202100:0001:00T12:4500:00T0
11Fredag10-12-202100:0001:00T14:4500:00T0
12Lørdag11-12-202100:0001:00T12:4515:00T19:000T8,25
13Søndag12-12-202117:4520:15R2,5
14Mandag13-12-202100:0008:30R6
15Tirsdag14-12-202107:3015:30FTG0
16Onsdag15-12-202107:3015:30FTG0
17Torsdag16-12-202107:3014:30FTG0
18Fredag17-12-202107:3013:30§170
19Lørdag18-12-20210
20Søndag19-12-20210
21Mandag20-12-202108:0017:00T0
22Tirsdag21-12-202107:3015:30T0
23Onsdag22-12-202107:3015:30T0
24Torsdag23-12-202107:3014:30T0
25Fredag24-12-202107:3013:30T0
26Lørdag25-12-202107:3015:30volunteer8
27Søndag26-12-202107:3015:30volunteer8
28Mandag27-12-202105:3015:30volunteer0,5
29Tirsdag28-12-202107:3015:30T0
30Onsdag29-12-202107:3015:30T0
31Torsdag30-12-202107:3014:30T0
32Fredag31-12-202107:3013:30T0
Ark1
Cell Formulas
RangeFormula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4AK2=(IF(OR(A2="Tirsdag",A2="Onsdag",A2="Torsdag",A2="Fredag",0),0))
AK5,AK26,AK19,AK12AK5=24*((IF(OR(A5="Lørdag",C5<TIME(0,0,0),C5="",D5=""),0,MAX(MIN((IF(D5<C5,1,D5)),TIME(0,0,0))-MAX(C5,0,0))))+(IF(OR(AND(D5<TIME(0,0,0),D5<>0),C5="",D5=""),0,MAX(MIN((IF(D5<C5,1,D5)),1)-MAX(C5,TIME(0,0,0),0))))+(IF(OR(F5>TIME(0,0,0),F5="",G5=""),0,MAX(MIN((IF(G5<F5,1,G5)),TIME(0,0,0))-MAX(F5,0,0))))+(IF(OR(AND(G5<TIME(0,0,0),G5<>0),F5="",G5=""),0,MAX(MIN((IF(G5<F5,1,G5)),1)-MAX(F5,TIME(0,0,0),0))))+(IF(OR(I5>TIME(0,0,0),I5="",J5=""),0,MAX(MIN((IF(J5<I5,1,J5)),TIME(0,0,0))-MAX(I5,0,0))))+(IF(OR(AND(J5<TIME(0,0,0),J5<>0),I5="",J5=""),0,MAX(MIN((IF(J5<I5,1,J5)),1)-MAX(I5,TIME(0,0,0),0)))))
AK6,AK27,AK20,AK13AK6=24*((IF(OR(A6="Søndag",C6<TIME(0,0,0),C6="",D6=""),0,MAX(MIN((IF(D6<C6,1,D6)),TIME(0,0,0))-MAX(C6,0,0))))+(IF(OR(AND(D6<TIME(0,0,0),D6<>0),C6="",D6=""),0,MAX(MIN((IF(D6<C6,1,D6)),1)-MAX(C6,TIME(0,0,0),0))))+(IF(OR(F6>TIME(0,0,0),F6="",G6=""),0,MAX(MIN((IF(G6<F6,1,G6)),TIME(0,0,0))-MAX(F6,0,0))))+(IF(OR(AND(G6<TIME(0,0,0),G6<>0),F6="",G6=""),0,MAX(MIN((IF(G6<F6,1,G6)),1)-MAX(F6,TIME(0,0,0),0))))+(IF(OR(I6>TIME(0,0,0),I6="",J6=""),0,MAX(MIN((IF(J6<I6,1,J6)),TIME(0,0,0))-MAX(I6,0,0))))+(IF(OR(AND(J6<TIME(0,0,0),J6<>0),I6="",J6=""),0,MAX(MIN((IF(J6<I6,1,J6)),1)-MAX(I6,TIME(0,0,0),0)))))
AK7,AK28,AK21,AK14AK7=IF(24*((IF(OR(A7="Mandag",C7>TIME(0,0,0),C7="",D7=""),MAX(MIN((IF(D7<C7,1,D7)),TIME(6,0,0))-MAX(C7,0,0)),0))+(IF(OR(F7>TIME(0,0,0),F7="",G7=""),0,MAX(MIN((IF(G7<F7,1,G7)),TIME(0,0,0))-MAX(F7,6,0),0))+IF(OR(I7>TIME(0,0,0),I7="",J7=""),0,MAX(MIN((IF(J7<I7,1,J7)),TIME(0,0,0))-MAX(I7,6,0),0))))<0,0,24*((IF(OR(A7="Mandag",C7>TIME(0,0,0),C7="",D7=""),MAX(MIN((IF(D7<C7,1,D7)),TIME(6,0,0))-MAX(C7,0,0)),0))+(IF(OR(F7>TIME(0,0,0),F7="",G7=""),0,MAX(MIN((IF(G7<F7,1,G7)),TIME(0,0,0))-MAX(F7,6,0),0))+IF(OR(I7>TIME(0,0,0),I7="",J7=""),0,MAX(MIN((IF(J7<I7,1,J7)),TIME(0,0,0))-MAX(I7,6,0),0)))))
 
Upvote 0
want2bexcel.xlsb
ABCDEFGHIJKAL
1UgedagDatoStart1Slut1Tidsart1Start2Slut2Tidsart2Start3Slut3Tidsart3UDF
2Onsdagwoensdag 01/12/2107:3015:30FTG0,00
3Torsdagdonderdag 02/12/2107:3014:30FTG0,00
4Fredagvrijdag 03/12/2107:3013:30FTG0,00
5Lørdagzaterdag 04/12/2112:4515:00T0,00
6Søndagzondag 05/12/2100:0001:00T12:4515:00T19:0024:00T6,00
7Mandagmaandag 06/12/2106:4508:30R0,00
8Tirsdagdinsdag 07/12/2100:0001:00T14:4524:00T0,00
9Onsdagwoensdag 08/12/2100:0001:00T14:4524:00T0,00
10Torsdagdonderdag 09/12/2100:0001:00T12:4524:00T0,00
11Fredagvrijdag 10/12/2100:0001:00T14:4524:00T0,00
12Lørdagzaterdag 11/12/2100:0001:00T12:4515:00T19:0024:00T6,00
13Søndagzondag 12/12/2117:4520:15R2,50
14Mandagmaandag 13/12/2100:0008:30R6,00
15Tirsdagdinsdag 14/12/2107:3015:30FTG0,00
16Onsdagwoensdag 15/12/2107:3015:30FTG0,00
17Torsdagdonderdag 16/12/2107:3014:30FTG0,00
18Fredagvrijdag 17/12/2107:3013:30§170,00
19Lørdagzaterdag 18/12/210,00
20Søndagzondag 19/12/210,00
21Mandagmaandag 20/12/2108:0017:00T0,00
22Tirsdagdinsdag 21/12/2107:3015:30T0,00
23Onsdagwoensdag 22/12/2107:3015:30T0,00
24Torsdagdonderdag 23/12/2107:3014:30T0,00
25Fredagvrijdag 24/12/2107:3013:30T0,00
26Lørdagzaterdag 25/12/2107:3015:30volunteer0,00
27Søndagzondag 26/12/2107:3015:30volunteer0,00
28Mandagmaandag 27/12/2105:3015:30volunteer0,00
29Tirsdagdinsdag 28/12/2107:3015:30T0,00
30Onsdagwoensdag 29/12/2107:3015:30T0,00
31Torsdagdonderdag 30/12/2107:3014:30T0,00
32Fredagvrijdag 31/12/2107:3013:30T0,00
Blad2
Cell Formulas
RangeFormula
AL2:AL32AL2=w2be(Tabel1[@[Ugedag]:[Tidsart3]])


VBA Code: 
Function W2BE(bereik As Range)
     Application.Volatile
     a = bereik.Value2                                          'read range to array
     If Weekday(a(1, 2), vbTuesday) <= 4 Then W2BE = 0: Exit Function     'tuesday to friday = nothing

     t1 = CDbl(TimeValue("06:00:00"))                           'end morning
     t2 = CDbl(TimeValue("17:00:00"))                           'start evening
     For i = 3 To 9 Step 3                                      'examine 3 timeslots
          If a(1, i + 2) <> "volunteer" And Len(a(1, i)) > 0 And Len(a(1, i + 1)) > 0 Then 'not volunteer in that period and both times present
               W2BE = W2BE + Application.Max(0, Application.Min(t1, a(1, i + 1)) - Application.Max(0, a(1, i))) 'add part in the morning
               W2BE = W2BE + Application.Max(0, Application.Min(1, a(1, i + 1)) - Application.Max(t2, a(1, i))) 'add part in the evening
            End If
     Next
     W2BE = W2BE * 24
End Function
 
Last edited:
Upvote 0
BSALV said:
this is already a start for the volunteers and the tuesday till friday.
What do you try to do with the formula, all the hours after 14:00 ?
Map1
AKAL
1TESTKolom1
20,000
30,000
40,000
51,00formula
68,25formula
7-0,75formula
80,000
90,000
100,000
110,000
127,25formula
132,50formula
146,00formula
150,000
160,000
170,000
180,000
190,00formula
200,00formula
21-2,00formula
220,000
230,000
240,000
250,000
261,500
278,000
280,500
290,000
300,000
310,000
320,000
Blad2
Cell Formulas
RangeFormula
AK2:AK4,AK8:AK11,AK15:AK18,AK22:AK25,AK29:AK32AK2=(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AL2:AL32AL2=IF(OR([@Tidsart1]="volunteer",WEEKDAY([@Dato],12)<=4),0,"formula")
AK5,AK12,AK19,AK26AK5=24*((IF(OR([@Ugedag]="Lørdag",[@Start1]<TIME(14,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(14,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(14,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(14,0,0),0))))+(IF(OR([@Start2]>TIME(14,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(14,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(14,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(14,0,0),0))))+(IF(OR([@Start3]>TIME(14,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(14,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(14,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(14,0,0),0)))))
AK6,AK13,AK20,AK27AK6=24*((IF(OR([@Ugedag]="Søndag",[@Start1]<TIME(0,0,0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(0,0,0))-MAX([@Start1],0,0))))+(IF(OR(AND([@Slut1]<TIME(0,0,0),[@Slut1]<>0),[@Start1]="",[@Slut1]=""),0,MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),1)-MAX([@Start1],TIME(0,0,0),0))))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],0,0))))+(IF(OR(AND([@Slut2]<TIME(0,0,0),[@Slut2]<>0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),1)-MAX([@Start2],TIME(0,0,0),0))))+(IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],0,0))))+(IF(OR(AND([@Slut3]<TIME(0,0,0),[@Slut3]<>0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),1)-MAX([@Start3],TIME(0,0,0),0)))))
AK7,AK14,AK21,AK28AK7=24*((IF(OR([@Ugedag]="Mandag",[@Start1]>TIME(0,0,0),[@Start1]="",[@Slut1]=""),MAX(MIN((IF([@Slut1]<[@Start1],1,[@Slut1])),TIME(6,0,0))-MAX([@Start1],0,0)),0))+(IF(OR([@Start2]>TIME(0,0,0),[@Start2]="",[@Slut2]=""),0,MAX(MIN((IF([@Slut2]<[@Start2],1,[@Slut2])),TIME(0,0,0))-MAX([@Start2],6,0),0))+IF(OR([@Start3]>TIME(0,0,0),[@Start3]="",[@Slut3]=""),0,MAX(MIN((IF([@Slut3]<[@Start3],1,[@Slut3])),TIME(0,0,0))-MAX([@Start3],6,0),0))))
Click to expand...
I was trying to make the
- formula on Saturdays [that is Lørdag] count the hours from 14:00 to 00:00
- formula on Sundays [that is Søndag] count the hours from 00:00 to 00:00
- formula on Mondays [that is Mandag] count the hours from 00:00 to 06:00
and when they all worked, I would try and combine them into 1 LONG formula, so all would have the same, but results would be diffenrent depending on the data
 
Upvote 0
and what is Startx and Slutx ? inclusive or exclusive ?
 
Upvote 0
want2bexcel.xlsb
ABCDEFGHIJKAL
1UgedagDatoStart1Slut1Tidsart1Start2Slut2Tidsart2Start3Slut3Tidsart3UDF
2Onsdagwoensdag 01/12/2107:3015:30FTG0,00
3Torsdagdonderdag 02/12/2107:3014:30FTG0,00
4Fredagvrijdag 03/12/2107:3013:30FTG0,00
5Lørdagzaterdag 04/12/2112:4515:00T1,00
6Søndagzondag 05/12/2100:0001:00T12:4515:00T19:0024:00T8,25
7Mandagmaandag 06/12/2106:4508:30R0,00
8Tirsdagdinsdag 07/12/2100:0001:00T14:4524:00T0,00
9Onsdagwoensdag 08/12/2100:0001:00T14:4524:00T0,00
10Torsdagdonderdag 09/12/2100:0001:00T12:4524:00T0,00
11Fredagvrijdag 10/12/2100:0001:00T14:4524:00T0,00
12Lørdagzaterdag 11/12/2100:0001:00T12:4515:00T19:0024:00T6,00
13Søndagzondag 12/12/2117:4520:15R2,50
14Mandagmaandag 13/12/2100:0008:30R6,00
15Tirsdagdinsdag 14/12/2107:3015:30FTG0,00
16Onsdagwoensdag 15/12/2107:3015:30FTG0,00
17Torsdagdonderdag 16/12/2107:3014:30FTG0,00
18Fredagvrijdag 17/12/2107:3013:30§170,00
19Lørdagzaterdag 18/12/210,00
20Søndagzondag 19/12/210,00
21Mandagmaandag 20/12/2108:0017:00T0,00
22Tirsdagdinsdag 21/12/2107:3015:30T0,00
23Onsdagwoensdag 22/12/2107:3015:30T0,00
24Torsdagdonderdag 23/12/2107:3014:30T0,00
25Fredagvrijdag 24/12/2107:3013:30T0,00
26Lørdagzaterdag 25/12/2107:3015:30volunteer0,00
27Søndagzondag 26/12/2107:3015:30volunteer0,00
28Mandagmaandag 27/12/2105:3015:30volunteer0,00
29Tirsdagdinsdag 28/12/2107:3015:30T0,00
30Onsdagwoensdag 29/12/2107:3015:30T0,00
31Torsdagdonderdag 30/12/2107:3014:30T0,00
32Fredagvrijdag 31/12/2107:3013:30T0,00
Blad2
Cell Formulas
RangeFormula
AL2:AL32AL2=w2be(Tabel1[@[Ugedag]:[Tidsart3]])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AL1:AL32Expression=AL1=0textNO

VBA Code: 
Function W2BE(bereik As Range)
     Application.Volatile
     a = bereik.Value2                                          'read range to array
     wd = Weekday(a(1, 2), vbTuesday)
     If wd <= 4 Then W2BE = 0: Exit Function                    'tuesday to friday = nothing

     t1 = CDbl(TimeValue("06:00:00"))                           'end morning
     t2 = CDbl(TimeValue("14:00:00"))                           'start evening
     For i = 3 To 9 Step 3                                      'examine 3 timeslots
          If a(1, i + 2) <> "volunteer" And Len(a(1, i)) > 0 And Len(a(1, i + 1)) > 0 Then     'not volunteer in that period and both times present
               Select Case wd
                    Case 7: W2BE = W2BE + Application.Max(0, Application.Min(t1, a(1, i + 1)) - Application.Max(0, a(1, i)))     'add part in the morning for Mon
                    Case 6: W2BE = W2BE + Application.Max(0, Application.Min(1, a(1, i + 1)) - Application.Max(0, a(1, i)))     'add part in the day for Sun
                    Case 5: W2BE = W2BE + Application.Max(0, Application.Min(1, a(1, i + 1)) - Application.Max(t2, a(1, i)))     'add part in the afternoon & evening for Sat
                    Case Else: MsgBox "impossible"
               End Select
          End If
     Next
     W2BE = W2BE * 24
End Function
 
Upvote 0
Solution
BSALV said:
want2bexcel.xlsb
ABCDEFGHIJKAL
1UgedagDatoStart1Slut1Tidsart1Start2Slut2Tidsart2Start3Slut3Tidsart3UDF
2Onsdagwoensdag 01/12/2107:3015:30FTG0,00
3Torsdagdonderdag 02/12/2107:3014:30FTG0,00
4Fredagvrijdag 03/12/2107:3013:30FTG0,00
5Lørdagzaterdag 04/12/2112:4515:00T1,00
6Søndagzondag 05/12/2100:0001:00T12:4515:00T19:0024:00T8,25
7Mandagmaandag 06/12/2106:4508:30R0,00
8Tirsdagdinsdag 07/12/2100:0001:00T14:4524:00T0,00
9Onsdagwoensdag 08/12/2100:0001:00T14:4524:00T0,00
10Torsdagdonderdag 09/12/2100:0001:00T12:4524:00T0,00
11Fredagvrijdag 10/12/2100:0001:00T14:4524:00T0,00
12Lørdagzaterdag 11/12/2100:0001:00T12:4515:00T19:0024:00T6,00
13Søndagzondag 12/12/2117:4520:15R2,50
14Mandagmaandag 13/12/2100:0008:30R6,00
15Tirsdagdinsdag 14/12/2107:3015:30FTG0,00
16Onsdagwoensdag 15/12/2107:3015:30FTG0,00
17Torsdagdonderdag 16/12/2107:3014:30FTG0,00
18Fredagvrijdag 17/12/2107:3013:30§170,00
19Lørdagzaterdag 18/12/210,00
20Søndagzondag 19/12/210,00
21Mandagmaandag 20/12/2108:0017:00T0,00
22Tirsdagdinsdag 21/12/2107:3015:30T0,00
23Onsdagwoensdag 22/12/2107:3015:30T0,00
24Torsdagdonderdag 23/12/2107:3014:30T0,00
25Fredagvrijdag 24/12/2107:3013:30T0,00
26Lørdagzaterdag 25/12/2107:3015:30volunteer0,00
27Søndagzondag 26/12/2107:3015:30volunteer0,00
28Mandagmaandag 27/12/2105:3015:30volunteer0,00
29Tirsdagdinsdag 28/12/2107:3015:30T0,00
30Onsdagwoensdag 29/12/2107:3015:30T0,00
31Torsdagdonderdag 30/12/2107:3014:30T0,00
32Fredagvrijdag 31/12/2107:3013:30T0,00
Blad2
Cell Formulas
RangeFormula
AL2:AL32AL2=w2be(Tabel1[@[Ugedag]:[Tidsart3]])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AL1:AL32Expression=AL1=0textNO

VBA Code: 
Function W2BE(bereik As Range)
     Application.Volatile
     a = bereik.Value2                                          'read range to array
     wd = Weekday(a(1, 2), vbTuesday)
     If wd <= 4 Then W2BE = 0: Exit Function                    'tuesday to friday = nothing

     t1 = CDbl(TimeValue("06:00:00"))                           'end morning
     t2 = CDbl(TimeValue("14:00:00"))                           'start evening
     For i = 3 To 9 Step 3                                      'examine 3 timeslots
          If a(1, i + 2) <> "volunteer" And Len(a(1, i)) > 0 And Len(a(1, i + 1)) > 0 Then     'not volunteer in that period and both times present
               Select Case wd
                    Case 7: W2BE = W2BE + Application.Max(0, Application.Min(t1, a(1, i + 1)) - Application.Max(0, a(1, i)))     'add part in the morning for Mon
                    Case 6: W2BE = W2BE + Application.Max(0, Application.Min(1, a(1, i + 1)) - Application.Max(0, a(1, i)))     'add part in the day for Sun
                    Case 5: W2BE = W2BE + Application.Max(0, Application.Min(1, a(1, i + 1)) - Application.Max(t2, a(1, i)))     'add part in the afternoon & evening for Sat
                    Case Else: MsgBox "impossible"
               End Select
          End If
     Next
     W2BE = W2BE * 24
End Function
Click to expand...

First thing... I can't use macros in this workbook, since there's a very good chance that some users will be using it on systems that don't allow macros.

Nevertheless, I have looked at it (the first one you provided) for hours, trying to make it work, since I'm trying to learn some VBA. But I can't even get it to run. When I make a macro (procedure) with your code and try to run it, I get an error in the very first line. Then I remembered there was a formula in the cell to. But I can't type in the formula in the cells. It wouldn't allow =w2be( as a function. It doesn't know it. I'm sure it's because I don't do it correctly.
I'm guessing that the code should create a new function =w2be if I did it correctly.

I normally use Excel 2016, but right now I'm on an old laptop with only Excel 2013, so I don't know if that could be an issue.
 
Upvote 0
example file
correct, you call that UDF in the worksheet with for example
Excel Formula: 
=w2be(Tabel1[@[Ugedag]:[Tidsart3]])
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
A
  • Question Question
Find the difference in days, hours and minutes between two dates and times
Replies
5
Views
1K
Dave Patton
D
S
  • Question Question
Countifs function for Date & Time Range not counting all cells
2
Replies
12
Views
2K
SMcCafferty
S
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
L
  • Question Question
Formula to calculate total numbers of days Spent from Departure Date& Time and Arrival Date and time
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top