MoPatel


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.




Re: Excel Time Help

ADG


hI

Have a look at the datediff function, you can get the return value in minutes for time difference.






Re: Excel Time Help

Firemangizmo

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))

23.00 6.00 7.00






Re: Excel Time Help

Firemangizmo

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.





Re: Excel Time Help

Firemangizmo

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.





Re: Excel Time Help

MoPatel

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





Re: Excel Time Help

MoPatel

Hi all,

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.