Problem in counting hours of work in employee salary form.

I am making an electronical salary form for my employees to fill out. As they will be using a tablet to fill out the form, I can't use macros for any functions.

The form has the following functions:
- A determines day of the week, ie "ddd".
- B determines date, defined by simple numbers.
- C is simple text, used as code for shift leaders, or other bonus pay, see J.
- D, E, F is text, any notifications about the shift, etc.
- G determines the start of the shift, f.ex. 18:00
- H determines the end of the shift, f.ex. 02:00
- I counts number of hours worked, in this example 8.
- J applies bonus pay, as determined by C.
- K determines the amount of extra pay for working from 18:00h on fridays, and all hours throughout shifts starting on sundays, counted in number of hours worked.

My problem is getting excel to count hours after 18:00 in the K column, without it counting all hours after 18:00h, even if the shift were to start f.ex. at 19:00h or 23:00h.

I've used this code:

=If(OR(G17="";H17="";I17="";);"";IF(IF(OR(TEXT(A17;"ddd")="fr";TEXT(A17;"ddd")="sa";TEXT(A17;"ddd")="su");I17-(IF(TEXT(A17;"ddd")="fr";18-((G17)*24);0));0)>0;IF(OR(TEXT(A17;"ddd")="fr";TEXT(A17;"ddd")="sa";TEXT(A17;"ddd")="su");I17-(IF(TEXT(A17;"ddd")="fr";18-((G17)*24);0));0);" "))

(I use a Norwegian version of Excel, so I apologize in advance if I've mistranslated any code.)

This code works if the shift on a friday starts before 18:00h (it will start counting hours from 18:00h onwards). But if it starts at a later time, f.ex. at 19:00h, it will count the number of hours from 18:00h, regardless if the shift (G column) starts at a later time.

Any ideas on how to fix this?

Regards, Heine

Is it possible to use the MAX function to only count the number of hours worked after 18:00?

Hello
Greetings for the day..!
Can you please give an example of the result you want in K column or either attach your workbook with results written manually to understand your problem more precisely...
Regards
Sourabh Gupta


Thanks for submitting your file....
I will try to solve your problem

Mal Timeliste2.xlsx

Here is an updated version, as you requested. Comments are included in the file.

=I17-(18-((G17)*24)) returns 8 as you said!
You have a number of issues:
1).When I import it in Xcel(US) the dates translate into Fri, Sat, Sun aso, while you test fixed text like "Fr".
If it is needed to use your solution internationally you must change your formula as such that it automatically changes into the local date settings.
2). You want to count the hours after 18.00 hrs to allow for overtime!
When this is the case you must include a test like =IF(g17*24>18,g17)
You have 4 possibilities to verify: B AND E < 18:00 ,, B <18:00 E>18:00 aso,,
After that I suggest you calculate the difference!
suggestion: start 12/12/2014 19:00 end 13/12/2014 02:00:00 when you enter the time like this your formulas become simpler!However data entering is a little more complicated!
But you avoid problems when working from let's say 23:59 until 23:59:59 + more than 24 hrs.

=IF(G17*24>18,IF(H17*24>G17*24,H17*24-G17*24),)
This is a partial solution. But it gives the correct result for 1 of the combinations of start and end time.
Now you must be able to build yr solution yourself I guess!
Hope this was helpful.

bart

please submit complete xcel file!

Mal Timeliste.xlsx

The only problem here, is that the file is in norwegian, so it may not open correctly.

Hello
Greetings for the day..!
Can you please give an example of the result you want in K column or either attach your workbook with results written manually to understand your problem more precisely...

Regards
Sourabh Gupta

OK. I can adapt the formulas. I can read enough Norwegian.

Put this in K13 and copy it down.....
=IF(AND(G13="",H13=""),"",IF(WEEKDAY(A13)=6,IF(G13*24<=18,H13*24-18,(H13-G13)*24),""))

I dont know exactly what you want....but I have tried
See it and tell if this is what you want

Note>>It only works for fridays

Regards

Using

=IF(AND(G13="",H13=""),"",IF(WEEKDAY(A13)=6,IF(G13*24<=18,H13*24-18,(H13-G13)*24),""))

how, do I incorporate IF(OR(WEEKDAY(A13)=7;WEEKDAY(A13)=1);(H13-G13)

I can't seem to get it to work.

PS: The reason I used ddd="fri", etc. was that it seems it's not sensitive to when the time goes over midnight.

hope this helps