I have a schedule that is in 24 hour clock and the cells are formatted using Custom 0000 to allow me to input the schedule without the use of the colon.
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Mon-Tue[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Time In
Between
Days[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0600[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]0700[/TD]
[TD="align: center"]1530[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]16.5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]0130[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]4.0[/TD]
[TD="align: center"]8.5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]0600-1000[/TD]
[TD="align: center"]1800-2200[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]#value![/TD]
[TD="align: center"]#value![/TD]
[/TR]
</tbody>[/TABLE]
In cells U4:V6 I have the following formula;
This formula was created by JoeMo to subtract the Time In from the Time Out, taking into consideration that sometimes the Time Out value can be lower than the Time In (eg- cell E5 on Monday is past midnight). The formula also subtracts 0.5 from all results that are longer than 5.4 hours in length (for staff lunch breaks).
In cells AC4:AC6 I have the following formula;
A very similar formula also created by JoeMo in the same thread I linked to above, this calculates the time in-between two shifts / days by subtracting Mondays Out time from Tuesday In time, also taking into consideration that some shifts will be ending before midnight, and some after.
So far, this is all working amazing! The only issue I am coming into is that sometimes staff are required to work a split shift - ie two shifts on the same day.
Right now, I am entering this as shown in cells F6:G6 as this is the easiest for the staff to understand, however this obviously throws all of my formulas out of the window as they cannot make sense of it!
I have done some extensive digging around looking for a solution and the closest thing I can find is this thread;
http://www.mrexcel.com/forum/excel-...ple-numbers-contained-within-single-cell.html
Does anyone know of a way to incorporate this into my formulas? Or perhaps a completely different way of going about finding a solution such as a VBA button that adds extra rows or something?
I have over 70 staff and there is almost always someone doing a split shift once a week so finding a solution to this issue is essential to my entire spreadsheet working (It is currently done manually (pencil & paper) by my boss & I am trying to roll out this spreadsheet in replacement).
ps- </d5,mod(dollarde(f5></d4,mod(dollarde(f4>*less-than-symbol* - The less than symbol does not show up on this forum for me, when I paste it in, it shows until I preview the post and then it deletes it along with the next 20 or so characters :/ very strange
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Mon-Tue[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Time In
Between
Days[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0600[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]0700[/TD]
[TD="align: center"]1530[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]16.5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]0130[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]4.0[/TD]
[TD="align: center"]8.5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]0600-1000[/TD]
[TD="align: center"]1800-2200[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]#value![/TD]
[TD="align: center"]#value![/TD]
[/TR]
</tbody>[/TABLE]
In cells U4:V6 I have the following formula;
Code:
=IF(E4="","",IF(MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)>5.4,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)-0.5,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)))
This formula was created by JoeMo to subtract the Time In from the Time Out, taking into consideration that sometimes the Time Out value can be lower than the Time In (eg- cell E5 on Monday is past midnight). The formula also subtracts 0.5 from all results that are longer than 5.4 hours in length (for staff lunch breaks).
In cells AC4:AC6 I have the following formula;
Code:
<d4,mod(dollarde(f4 100,60)-dollarde(e4="" 100,60),24),mod(dollarde(f4="" 100,60),24)+if(f4=""><d5,mod(dollarde(f5 100,60)-dollarde(e5="" 100,60),24),mod(dollarde(f5="" 100,60),24)+if(f5="">=IF(OR(E5="",F5=""),"",IF(E5[I][COLOR=#0000cd]*less-than-symbol*[/COLOR][/I]D5,MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24),MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24)+IF(F5>=E5,24,0)))
A very similar formula also created by JoeMo in the same thread I linked to above, this calculates the time in-between two shifts / days by subtracting Mondays Out time from Tuesday In time, also taking into consideration that some shifts will be ending before midnight, and some after.
So far, this is all working amazing! The only issue I am coming into is that sometimes staff are required to work a split shift - ie two shifts on the same day.
Right now, I am entering this as shown in cells F6:G6 as this is the easiest for the staff to understand, however this obviously throws all of my formulas out of the window as they cannot make sense of it!
I have done some extensive digging around looking for a solution and the closest thing I can find is this thread;
http://www.mrexcel.com/forum/excel-...ple-numbers-contained-within-single-cell.html
Does anyone know of a way to incorporate this into my formulas? Or perhaps a completely different way of going about finding a solution such as a VBA button that adds extra rows or something?
I have over 70 staff and there is almost always someone doing a split shift once a week so finding a solution to this issue is essential to my entire spreadsheet working (It is currently done manually (pencil & paper) by my boss & I am trying to roll out this spreadsheet in replacement).
ps- </d5,mod(dollarde(f5></d4,mod(dollarde(f4>*less-than-symbol* - The less than symbol does not show up on this forum for me, when I paste it in, it shows until I preview the post and then it deletes it along with the next 20 or so characters :/ very strange