Excel Time Help
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. |
Excel Time Help
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 On Wed, 06 Dec 2006 01:40:44 -0000, wrote: 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. |
Excel Time Help
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 _______ "SteveW" wrote in message news:op.tj37dcf3evjsnp@enigma03... 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 On Wed, 06 Dec 2006 01:40:44 -0000, wrote: 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. |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com