Hi all,
This is my first time posting here 
, hope i will be able to find the answer i am looking for. I'm very new to vba so please bear with me.
Here goes, this is the problem i'm having at the moment with my formulas and vba.
I am currently desinging a roster for my workplace and the problem i am having is to work out the night shifts. these night shift start at 23:00:00 and end at 06:00:00, now because there is a change in date between 23:00:00 and 00:00:00 im finding it very difficult to find the appropriate formula.
Let say the shift worked is starting at: 22:00:00 and ending at 07:00:00, what i would like excel to do is to calculate how many hours are worked from 22:00:00 to 23:00:00 and from 06:00:00 until 07:00:00 in one cell and in a different cell the time worked between 23:00:00 until 06:00:00, bearing in mind that the finish time can be before 06:00:00 and the start time could be after 23:00:00. Any one can help me please
any help would greatly be appreciated.
Excel Time Help
bes7252
If you place this calculation in column F it will calculate the time difference between start time, Previous Cell in column D and finish time, Previous Cell in column E. Once you do this and have your group of 3 calculating cells that you can then copy and paste to any location on the spreadsheet and Excel will automatically adjust for the changed position. Times are entered as hours (24 hour clock) seperated by a decimal point for the minutes which should only be in 15 minute increments. (.00, .15, .30, .45) The diagram at the bottom shows the form that mine takes.
=IF((INT(E1)+(E1-INT(E1))*100/60)-(INT(D1)+(D1-INT(D1))*100/60)<0,(INT(E1)+(E1-INT(E1))*100/60)-(INT(D1)+(D1-INT(D1))*100/60)+24,(INT(E1)+(E1-INT(E1))*100/60)-(INT(D1)+(D1-INT(D1))*100/60))
Andrew Mercer
thank you all for your replies but i wasnt successful with the datediff in vba and the other formula provided wasnt doing what i required. Althought i have found a formula that works and does what i want it to do
=IF(MOD(A2+1/12,1)<1/3,MIN(1/3,MOD(B2+1/12,1))-MOD(A2+1/12,1),IF(MOD(B2+1/1,1)<A2,MIN(MOD(B2+1/12,1),1/3),0))
the problem with this one though is that the moght shift will start at 22:00:00, not at 23:00:00 which is what i need. what should i do to change the time to 23:00:00
any suggestions
thanks
nomad&#35;1
Further to my previous reply, If you happen to use this calculation do not use Cut and Paste when program is running. Use Copy and Paste only and then Delete any unwanted data.
Dark Pontiac
hI
Have a look at the datediff function, you can get the return value in minutes for time difference.
HotKeeper
I also neglected to say that as well as placing calc in column F, it should also be in Row 1 (Cell F1). As I said previously, once you have it in place and calculating you can move the 3 cells together to where ever they need to go.
gregmackers
its me again, here is the solution to the problem
lets say C7 has the start shift and D7 the end shift, B28 contains the start of the night shift (it should be a time before midnight, in my case 23:00:00) and B29 the end of the night shift (06:00:00 for me)
the following formula goes in E7
=MOD(D7-C7,1)-((C7>D7)*MEDIAN(0,D7-$B$29,1-MOD($B$29-$B$28,1))+MAX(0,MIN($B$28,D7+(C7>D7))-MAX($B$29,C7)))
the formula above will give you the time worked as a night shift.
to get the time worked during the day, in F7 enter
=MOD(D7-C7,1)-E7
and this should do the trick
Thank to daddylongleg from Excelforum for providing this working formula.