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

Want2BExcel · Feb 12, 2022

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

A

B

C

D

E

F

G

H

I

J

K

AK

1

Ugedag

Dato

Start1

Slut1

Tidsart1

Start2

Slut2

Tidsart2

Start3

Slut3

Tidsart3

TEST

2

Onsdag

01-12-21

07:30

15:30

FTG

0

3

Torsdag

02-12-21

07:30

14:30

FTG

0

4

Fredag

03-12-21

07:30

13:30

FTG

0

5

Lørdag

04-12-21

12:45

15:00

T

1

6

Søndag

05-12-21

00:00

01:00

T

12:45

15:00

T

19:00

00:00

T

8,25

7

Mandag

06-12-21

06:45

08:30

R

-0,75

8

Tirsdag

07-12-21

00:00

01:00

T

14:45

00:00

T

0

9

Onsdag

08-12-21

00:00

01:00

T

14:45

00:00

T

0

10

Torsdag

09-12-21

00:00

01:00

T

12:45

00:00

T

0

11

Fredag

10-12-21

00:00

01:00

T

14:45

00:00

T

0

12

Lørdag

11-12-21

00:00

01:00

T

12:45

15:00

T

19:00

00:00

T

7,25

13

Søndag

12-12-21

17:45

20:15

R

2,5

14

Mandag

13-12-21

00:00

08:30

R

6

15

Tirsdag

14-12-21

07:30

15:30

FTG

0

16

Onsdag

15-12-21

07:30

15:30

FTG

0

17

Torsdag

16-12-21

07:30

14:30

FTG

0

18

Fredag

17-12-21

07:30

13:30

§17

0

19

Lørdag

18-12-21

0

20

Søndag

19-12-21

0

21

Mandag

20-12-21

08:00

17:00

T

-2

22

Tirsdag

21-12-21

07:30

15:30

T

0

23

Onsdag

22-12-21

07:30

15:30

T

0

24

Torsdag

23-12-21

07:30

14:30

T

0

25

Fredag

24-12-21

07:30

13:30

T

0

26

Lørdag

25-12-21

07:30

15:30

volunteer

1,5

27

Søndag

26-12-21

07:30

15:30

volunteer

8

28

Mandag

27-12-21

05:30

15:30

volunteer

0,5

29

Tirsdag

28-12-21

07:30

15:30

T

0

30

Onsdag

29-12-21

07:30

15:30

T

0

31

Torsdag

30-12-21

07:30

14:30

T

0

32

Fredag

31-12-21

07:30

13:30

T

0

Dec.21

Cell Formulas
Range	Formula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4	AK2	=(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AK5,AK26,AK19,AK12	AK5	=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,AK13	AK6	=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,AK14	AK7	=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))))

Want2BExcel · Feb 12, 2022

BSALV said:
example file
correct, you call that UDF in the worksheet with for example

Excel Formula:

=w2be(Tabel1[@[Ugedag]:[Tidsart3]])

I got it to work with this

Excel Formula:

=w2be([@Ugedag]:[@Tidsart3])

But not all results are correct (as they are on your examble). I have changed some times to test the functionallity. And added a column just to show you what all the correct results should be.

here is mine...

Arb.tid & Friheder TEST.xlsx

A

B

C

D

E

F

G

H

I

J

K

AK

AL

AM

1

Ugedag

Dato

Start1

Slut1

Tidsart1

Start2

Slut2

Tidsart2

Start3

Slut3

Tidsart3

TEST

UDF

2

Onsdag

01-12-21

07:30

15:30

FTG

0

3

Torsdag

02-12-21

07:30

14:30

FTG

0

4

Fredag

03-12-21

07:30

13:30

FTG

0

5

Lørdag

04-12-21

12:45

15:00

T

1

6

Søndag

05-12-21

00:00

01:00

T

12:45

15:00

T

19:00

00:00

T

8,25

3,25

8,25

7

Mandag

06-12-21

06:45

08:30

R

-0,75

0

8

Tirsdag

07-12-21

00:00

01:00

T

14:45

00:00

T

0

9

Onsdag

08-12-21

00:00

01:00

T

14:45

00:00

T

0

10

Torsdag

09-12-21

00:00

01:00

T

12:45

00:00

T

0

11

Fredag

10-12-21

00:00

01:00

T

14:45

00:00

T

0

12

Lørdag

11-12-21

00:00

01:00

T

12:45

15:00

T

19:00

00:00

T

7,25

1

5

13

Søndag

12-12-21

17:45

20:15

R

2,5

14

Mandag

13-12-21

00:00

03:30

T

04:30

05:00

T

05:30

08:00

T

3,5

4,5

15

Tirsdag

14-12-21

07:30

15:30

FTG

0

16

Onsdag

15-12-21

07:30

15:30

FTG

0

17

Torsdag

16-12-21

07:30

14:30

FTG

0

18

Fredag

17-12-21

07:30

13:30

§17

0

19

Lørdag

18-12-21

0

20

Søndag

19-12-21

0

21

Mandag

20-12-21

08:00

17:00

T

-2

0

22

Tirsdag

21-12-21

07:30

15:30

T

0

23

Onsdag

22-12-21

07:30

15:30

T

0

24

Torsdag

23-12-21

07:30

14:30

T

0

25

Fredag

24-12-21

07:30

13:30

T

0

26

Lørdag

25-12-21

07:30

15:30

volunteer

0

27

Søndag

26-12-21

07:30

15:30

volunteer

0

28

Mandag

27-12-21

05:30

15:30

volunteer

0

29

Tirsdag

28-12-21

07:30

15:30

T

0

30

Onsdag

29-12-21

07:30

15:30

T

0

31

Torsdag

30-12-21

07:30

14:30

T

0

32

Fredag

31-12-21

07:30

13:30

T

0

Dec.21

Cell Formulas
Range	Formula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4	AK2	=(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AL2:AL32	AL2	=w2be([@Ugedag]:[@Tidsart3])
AK5,AK26,AK19,AK12	AK5	=24*((IF([@Tidsart1]="volunteer",0,(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,AK13	AK6	=24*((IF([@Tidsart1]="volunteer",0,(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,AK14	AK7	=24*((IF([@Tidsart1]="volunteer",0,(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))))))

Want2BExcel · Feb 12, 2022

Want2BExcel said:
I got it to work with this

Excel Formula:

=w2be([@Ugedag]:[@Tidsart3])

But not all results are correct (as they are on your examble). I have changed some times to test the functionallity. And added a column just to show you what all the correct results should be.

here is mine...

Arb.tid & Friheder TEST.xlsx
A B C D E F G H I J K AK AL AM
1 Ugedag Dato Start1 Slut1 Tidsart1 Start2 Slut2 Tidsart2 Start3 Slut3 Tidsart3 TEST UDF
2 Onsdag 01-12-21 07:30 15:30 FTG 0 0 0
3 Torsdag 02-12-21 07:30 14:30 FTG 0 0 0
4 Fredag 03-12-21 07:30 13:30 FTG 0 0 0
5 Lørdag 04-12-21 12:45 15:00 T 1 1 1
6 Søndag 05-12-21 00:00 01:00 T 12:45 15:00 T 19:00 00:00 T 8,25 3,25 8,25
7 Mandag 06-12-21 06:45 08:30 R -0,75 0 0
8 Tirsdag 07-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
9 Onsdag 08-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
10 Torsdag 09-12-21 00:00 01:00 T 12:45 00:00 T 0 0 0
11 Fredag 10-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
12 Lørdag 11-12-21 00:00 01:00 T 12:45 15:00 T 19:00 00:00 T 7,25 1 5
13 Søndag 12-12-21 17:45 20:15 R 2,5 2,5 2,5
14 Mandag 13-12-21 00:00 03:30 T 04:30 05:00 T 05:30 08:00 T 3,5 4,5 4,5
15 Tirsdag 14-12-21 07:30 15:30 FTG 0 0 0
16 Onsdag 15-12-21 07:30 15:30 FTG 0 0 0
17 Torsdag 16-12-21 07:30 14:30 FTG 0 0 0
18 Fredag 17-12-21 07:30 13:30 §17 0 0 0
19 Lørdag 18-12-21 0 0 0
20 Søndag 19-12-21 0 0 0
21 Mandag 20-12-21 08:00 17:00 T -2 0 0
22 Tirsdag 21-12-21 07:30 15:30 T 0 0 0
23 Onsdag 22-12-21 07:30 15:30 T 0 0 0
24 Torsdag 23-12-21 07:30 14:30 T 0 0 0
25 Fredag 24-12-21 07:30 13:30 T 0 0 0
26 Lørdag 25-12-21 07:30 15:30 volunteer 0 0 0
27 Søndag 26-12-21 07:30 15:30 volunteer 0 0 0
28 Mandag 27-12-21 05:30 15:30 volunteer 0 0 0
29 Tirsdag 28-12-21 07:30 15:30 T 0 0 0
30 Onsdag 29-12-21 07:30 15:30 T 0 0 0
31 Torsdag 30-12-21 07:30 14:30 T 0 0 0
32 Fredag 31-12-21 07:30 13:30 T 0 0 0
Dec.21
Cell Formulas
Range Formula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4 AK2 =(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AL2:AL32 AL2 =w2be([@Ugedag]:[@Tidsart3])
AK5,AK26,AK19,AK12 AK5 =24*((IF([@Tidsart1]="volunteer",0,(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,AK13 AK6 =24*((IF([@Tidsart1]="volunteer",0,(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,AK14 AK7 =24*((IF([@Tidsart1]="volunteer",0,(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))))))

AM12 should be 6, sorry

but there only 2 errors...which I think it has something to do with my 00:00 in Slut3 (TimeEnding3)

ebea · Feb 13, 2022

Want2BExcel said:
I got it to work with this

Excel Formula:

=w2be([@Ugedag]:[@Tidsart3])

But not all results are correct (as they are on your examble). I have changed some times to test the functionallity. And added a column just to show you what all the correct results should be.

here is mine...

Arb.tid & Friheder TEST.xlsx
A B C D E F G H I J K AK AL AM
1 Ugedag Dato Start1 Slut1 Tidsart1 Start2 Slut2 Tidsart2 Start3 Slut3 Tidsart3 TEST UDF
2 Onsdag 01-12-21 07:30 15:30 FTG 0 0 0
3 Torsdag 02-12-21 07:30 14:30 FTG 0 0 0
4 Fredag 03-12-21 07:30 13:30 FTG 0 0 0
5 Lørdag 04-12-21 12:45 15:00 T 1 1 1
6 Søndag 05-12-21 00:00 01:00 T 12:45 15:00 T 19:00 00:00 T 8,25 3,25 8,25
7 Mandag 06-12-21 06:45 08:30 R -0,75 0 0
8 Tirsdag 07-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
9 Onsdag 08-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
10 Torsdag 09-12-21 00:00 01:00 T 12:45 00:00 T 0 0 0
11 Fredag 10-12-21 00:00 01:00 T 14:45 00:00 T 0 0 0
12 Lørdag 11-12-21 00:00 01:00 T 12:45 15:00 T 19:00 00:00 T 7,25 1 5
13 Søndag 12-12-21 17:45 20:15 R 2,5 2,5 2,5
14 Mandag 13-12-21 00:00 03:30 T 04:30 05:00 T 05:30 08:00 T 3,5 4,5 4,5
15 Tirsdag 14-12-21 07:30 15:30 FTG 0 0 0
16 Onsdag 15-12-21 07:30 15:30 FTG 0 0 0
17 Torsdag 16-12-21 07:30 14:30 FTG 0 0 0
18 Fredag 17-12-21 07:30 13:30 §17 0 0 0
19 Lørdag 18-12-21 0 0 0
20 Søndag 19-12-21 0 0 0
21 Mandag 20-12-21 08:00 17:00 T -2 0 0
22 Tirsdag 21-12-21 07:30 15:30 T 0 0 0
23 Onsdag 22-12-21 07:30 15:30 T 0 0 0
24 Torsdag 23-12-21 07:30 14:30 T 0 0 0
25 Fredag 24-12-21 07:30 13:30 T 0 0 0
26 Lørdag 25-12-21 07:30 15:30 volunteer 0 0 0
27 Søndag 26-12-21 07:30 15:30 volunteer 0 0 0
28 Mandag 27-12-21 05:30 15:30 volunteer 0 0 0
29 Tirsdag 28-12-21 07:30 15:30 T 0 0 0
30 Onsdag 29-12-21 07:30 15:30 T 0 0 0
31 Torsdag 30-12-21 07:30 14:30 T 0 0 0
32 Fredag 31-12-21 07:30 13:30 T 0 0 0
Dec.21
Cell Formulas
Range Formula
AK29:AK32,AK22:AK25,AK15:AK18,AK8:AK11,AK2:AK4 AK2 =(IF(OR([@Ugedag]="Tirsdag",[@Ugedag]="Onsdag",[@Ugedag]="Torsdag",[@Ugedag]="Fredag",0),0))
AL2:AL32 AL2 =w2be([@Ugedag]:[@Tidsart3])
AK5,AK26,AK19,AK12 AK5 =24*((IF([@Tidsart1]="volunteer",0,(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,AK13 AK6 =24*((IF([@Tidsart1]="volunteer",0,(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,AK14 AK7 =24*((IF([@Tidsart1]="volunteer",0,(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))))))

I can't understand, that the results should be correct, for the test you have made in example #11. How can Saturday 11-12-2021 be 5 hours ? It should by a normally count, be 8,25. Or?

Want2BExcel · Feb 13, 2022

ebea said:
I can't understand, that the results should be correct, for the test you have made in example #11. How can Saturday 11-12-2021 be 5 hours ? It should by a normally count, be 8,25. Or?

Hi ebea,
You probably didn't saw this ? and it should be 6 as I correct edited in #12. Because it's a Saturday, the count should only take the hours FROM 14:00 to 00:00
I also wrote it in bold, in my first line in question #1

Want2BExcel said:
- 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

BSALV · Feb 13, 2022

General conclusion, my UDF now makes no errors if you use 23:59:59 or just 1 (to have 24:00:00, perhaps better use numberformat [hh]:mm, like i already did in #4 and #7) ?

With an excel 2016, the newest formulas (LET, ...) aren't available, so that 'll be a long formula.
I stop here, because such long formulas, i'm not a big fan.
But as a way to simplify the formula, add 2 aux. columns in which you calculate for every sat-mon the start hour for sat 14:00 and mon 6:00. That 'll shorten your formula with the 50%

BSALV · Feb 13, 2022

you start writing and the formula is almost there

want2bexcel.xlsb

AL

AM

AN

AO

1

UDF

Sat

Mon

test2

2

3

4

5

6

7

8

9

Blad2

Cell Formulas
Range	Formula
AL2:AL9	AL2	=w2be(Tabel1[@[Ugedag]:[Tidsart3]])
AM2:AM9	AM2	=IF(WEEKDAY([@Dato],16)<=3,[@Dato]-WEEKDAY([@Dato],16)+1+TIME(14,0,0),"-")
AN2:AN9	AN2	=IF(WEEKDAY([@Dato],16)<=3,[@Dato]-WEEKDAY([@Dato],16)+3+TIME(6,0,0),"-")
AO2:AO9	AO2	=IF(ISNUMBER([@Sat]),24*( IF(COUNT(Tabel1[@[Start1]:[Slut1]])=2,MAX(0,MIN([@Slut1]+[@Dato],[@Mon])-MAX([@Sat],[@Dato]+[@Start1])),0)+ IF(COUNT(Tabel1[@[Start2]:[Slut2]])=2,MAX(0,MIN([@Slut2]+[@Dato],[@Mon])-MAX([@Sat],[@Dato]+[@Start2])),0)+ IF(COUNT(Tabel1[@[Start3]:[Slut3]])=2,MAX(0,MIN([@Slut3]+[@Dato],[@Mon])-MAX([@Sat],[@Dato]+[@Start3])),0)), "-")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL1:AL32	Expression	=AL1=0	text	NO

w2be
with a screenshot of the dutch formula, you see better how it works

Want2BExcel · Feb 13, 2022

BSALV said:
General conclusion, my UDF now makes no errors if you use 23:59:59 or just 1 (to have 24:00:00, perhaps better use numberformat [hh]:mm, like i already did in #4 and #7) ?

With an excel 2016, the newest formulas (LET, ...) aren't available, so that 'll be a long formula.
I stop here, because such long formulas, i'm not a big fan.
But as a way to simplify the formula, add 2 aux. columns in which you calculate for every sat-mon the start hour for sat 14:00 and mon 6:00. That 'll shorten your formula with the 50%

Sometimes I can be so focused on a solution, that I can't see the obvious ??
I was so close ?? but as you pointed out, the formulas was incredible long and that was even before I was at a point where I could combine them.
I got the Mondays, Sundays and Tuesday-Friday to work, each by it self, but the Saturdays gave me a headache ?
I could make Saturday work when I broke it up, timeblock by timeblock, but when I tried putting the formulas together, I had the weirdest results, because the formuls worked differently when put together ? I could see where the problem was, but I didn't understand why since they worked fine, each on its own ? probably missed a "few" )))))((((<> somewhere.
So I did what you suggested and made some aux. colums and SUM them afterwards....so easy! I'm embarrassed that I didn't do that days ago. Well, lessons learned ?
I saved your UDF macro, for personal use, since it work fine now ?? it's also the one I mark as the solution, since it's the best way to solve a problem like this.
BSALV, Thank you for your patience and time ?
I learned a great deal ???

BSALV · Feb 13, 2022

as you can see in the screenshot, in the formula bar, you can add several "ALT+Enter" after a part of your formula, so the different parts stand all on a different line and are easy to compare/modify.
Therefore click on that arrow-down symbol at the right of the formulabar.
Those "ALT+Enter" have only visual effect, but doesn't change the calculations.

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

Want2BExcel

Board Regular

Want2BExcel

Board Regular

Excel Facts

Want2BExcel

Board Regular

ebea

Active Member

Want2BExcel

Board Regular

BSALV

Banned user

BSALV

Banned user

Attachments

Want2BExcel

Board Regular

BSALV

Banned user

Similar threads

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

Board Regular

Active Member

Board Regular

Banned user

Banned user

Attachments

Board Regular

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock