Here is a hard question. Can you solve it?

I want to create an Excile-document where I can put the time I work (example 13:45 - 23:00 on Friday or 21:30 - 07:00 Friday to Saturday) and the document will then calculate OB hours depending on factors time and what day it is. How do I program Excel to do it?

I know the following:

OB:

Weekday evening: Monday, Tuesday, Wednesday, Thursday, between 19:00 and 00:00.

Weekday night: Monday, Tuesday, Wednesday, Thursday, between 00:00 - 06:00.

Weekend: Friday, 19:00 - 00:00 and Saturday, Sunday 06:00 - 00:00.

Weekend night: Friday, Saturday, 00:00 - 06:00.

Example 1:

Operates 21:30 - 07:00 from Friday to Saturday. Then the Excel document view:

Total hours:

9.5 hours.

OB Additional Weekend: 21:30 - 00:00 and 06:00 - 07:00:

3.5 hours.

OB Additional Weekend night: 00:00 - 06:00:

6 hours.

Example 2:

Operates 21:30 - 07:00 from Monday to Tuesday. Then the Excel document view:

Total hours:

9.5 hours

OB Additional weeknight: 21:30 - 00:00:

2.5 hours

OB Supplement Everyday night: 00:00 - 06:00

6 hours.

Example 3:

Operates 13:45 - 21:30 Monday. Then the Excel document view:

Total hours:

7.75 hours.

OB Additional weeknight: 19:00 - 21:30

2.5 hours.

MVH

T

Hi,

The request is hard to be realize on Excel only, it may involve some knowledge of Excel development - Also something related to Windows development.

If you have any problem in developing the function with your code, you can post the question in Excel for Developers forum, the experts there can point you to the right direction:

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

Regards,

Melon Chen
TechNet Community Support