Excel Time Help

[moubarakpatel]

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 and Excel formulas.

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.
I have tried DateDiff and DateDif and i still have no clue how this is
done.

help me please?

any help would greatly be appreciated.

 

[SteveW]

First, this is probably the 30th time it's been asked and resolved in the
last couple of months.

So, use the benefits of search on this forum.

Excel is quite capable of doing this with =IF()

A2=23:00 B2=06:00 C2=IF(B2<A2,B2+1-A2,B2-A2)

Excel stores time as a fraction of a day, so 6:00 is 6/24 of a day

Does't work for workoholics who start at 22:00 one day and finish at 23:00
the next
but hope that doesn't happen often

Steve

ps neater answers are available, but this shows the calculation behind them

 

[Jon Peltier]

Even neater: Enter the date and time together in each cell, like 12/5/2006
21:00.

The date is a whole number, representing the number of days since 1/1/1900,
and the time, as Steve pointed out, is a fraction, the fraction of the day
elapsed since midnight..

So if you're a workaholic who started at 22:00 on 12/2/06 and finished at
23:00 on 12/4/06, you can easily see that the elapsed time was 49 hours.
Format using [h]:mm to get the result to appear as 49:00.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


First, this is probably the 30th time it's been asked and resolved in the
last couple of months.

So, use the benefits of search on this forum.

Excel is quite capable of doing this with =IF()

A2=23:00 B2=06:00 C2=IF(B2<A2,B2+1-A2,B2-A2)

Excel stores time as a fraction of a day, so 6:00 is 6/24 of a day

Does't work for workoholics who start at 22:00 one day and finish at 23:00
the next
but hope that doesn't happen often

Steve

ps neater answers are available, but this shows the calculation behind them