| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
I have a table where the time is to be added for the week. This poses
no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time
If start time is A2, Finish time is B2 then
=B2+IF(A2B2,1,0)-A2 Regards, Stefi €˛Fortune€¯ ezt Ć*rta: I have a table where the time is to be added for the week. This poses no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time
=MOD(C3-B3,1)
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Fortune" wrote in message oups.com... I have a table where the time is to be added for the week. This poses no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time
Hi
Instead of taking one time from the other as in =B1-A1 use =MOD(B1-A1,1) That will deal with both cases. -- Regards Roger Govier "Fortune" wrote in message oups.com... I have a table where the time is to be added for the week. This poses no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time
It's really clever!
Stefi €˛Bob Phillips€¯ ezt Ć*rta: =MOD(C3-B3,1) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Fortune" wrote in message oups.com... I have a table where the time is to be added for the week. This poses no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding time
.....or
=B1-A1+(A1B1) "Roger Govier" wrote: Hi Instead of taking one time from the other as in =B1-A1 use =MOD(B1-A1,1) That will deal with both cases. -- Regards Roger Govier "Fortune" wrote in message oups.com... I have a table where the time is to be added for the week. This poses no problem. But I have difficulty when there is a night shift. I have given below an example Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Start Finish Mon Tue Wed Thurs Fri Sat Sun Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 off off Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off off off off As can be seen from the above example, supervisor 1 works for 40 hours a week, and supervisor 2 works for 36 hours a week. I am looking for a single formula that will deduct 17:00-09:00 for each day which will give a row total of 40 hours. But at the same time it needs to be applied to the second supervisor who works the night shift and completes at 7 am in the morning. This is creating difficulty as the 07:00 is less than 19:00 the previous day. Any ideas out there. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding Time Function | Excel Worksheet Functions | |||
| adding a code to calculate how much time is lost | Excel Discussion (Misc queries) | |||
| Adding time | Excel Worksheet Functions | |||
| what is the formula for adding up time intervals? | Excel Discussion (Misc queries) | |||
| adding in time formats | Excel Discussion (Misc queries) | |||
Linear Mode
