|
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. |
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 05:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com