time calculations #6

Hello!

Does anyone know how you can calculate worked hours in a day on the
basis of a start and end time?

0
9/26/2005 8:46:20 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
236 Views

Similar Articles

[PageSpeed] 54

=(end_time-start_time)*24

-- 
 HTH

Bob Phillips

"Aline" <A_Mangelschots@hotmail.com> wrote in message
news:1127724380.330877.175210@o13g2000cwo.googlegroups.com...
> Hello!
>
> Does anyone know how you can calculate worked hours in a day on the
> basis of a start and end time?
>


0
bob.phillips1 (6510)
9/26/2005 8:53:20 AM
Hi Aline

With start time in column A, and end time in column B, enter in C1
=MOD((B1-A1),1)
will give the time interval, even allowing for time period where the start 
is on one day, and the finish is on the next.
Input time on a 24 hour basis 16:30 for 4:30 pm.

If you want to sum the hours worked, then just =SUM(C1:C100) but format the 
cell with the formula
Format Cells>Number>Custom  as  [hh]:mm to allow Excel to sum past 24 hours.

Change ranges to suit.

Regards

Roger Govier


Aline wrote:
> Hello!
> 
> Does anyone know how you can calculate worked hours in a day on the
> basis of a start and end time?
> 
0
roger1272 (620)
9/26/2005 9:00:03 AM
Thanks Bob,

But it does not work on my example. For instance, starting time is
22.00hr, end time 06.00 hr; total worked hours would have to result in
8 hrs.

Bob Phillips schreef:

> =(end_time-start_time)*24
>
> --
>  HTH
>
> Bob Phillips
>
> "Aline" <A_Mangelschots@hotmail.com> wrote in message
> news:1127724380.330877.175210@o13g2000cwo.googlegroups.com...
> > Hello!
> >
> > Does anyone know how you can calculate worked hours in a day on the
> > basis of a start and end time?
> >

0
9/26/2005 9:02:23 AM
=((end_time-start_time)+if(end_time<start_time,1,0))*24
or shorter:
=((end_time-start_time)+(end_time<start_time))*24

Another option would be to include the time and date for each entry.

Aline wrote:
> 
> Thanks Bob,
> 
> But it does not work on my example. For instance, starting time is
> 22.00hr, end time 06.00 hr; total worked hours would have to result in
> 8 hrs.
> 
> Bob Phillips schreef:
> 
> > =(end_time-start_time)*24
> >
> > --
> >  HTH
> >
> > Bob Phillips
> >
> > "Aline" <A_Mangelschots@hotmail.com> wrote in message
> > news:1127724380.330877.175210@o13g2000cwo.googlegroups.com...
> > > Hello!
> > >
> > > Does anyone know how you can calculate worked hours in a day on the
> > > basis of a start and end time?
> > >

-- 

Dave Peterson
0
petersod (12005)
9/26/2005 12:30:38 PM
Reply:

Similar Artilces:

Excel stopped Calculations???
I'm running Excel 2000 build 9.0.272 I was working on my spreadsheet, all of a sudden it appeared to crash. So i rebooted my machine. When I did so, it stopped making calculations. :( Very strange (an very bad). I can't get it to do _any_ calculations, either n th spreadsheet I was working on or any other spreadsheet. I can't figure out how to make it re-calculate the formulas again. Everything is intact, my formulas, values, etc... thank you for your help -- Message posted from http://www.ExcelForum.com From the menu at the top, select: Tools Options Click on tab marked...

Time calculations for Scheduled vs Actual Employee Time and Wages 11-28-09
I am THRYING and TRYING to make a simple schedule worksheet for a manger to use to calculate the time he schedules an employee to clock in and out and what it will cost him in regards to his allocated budget and then to be able to compare it after the job is completed. BUT time calculations have thrown me for loop and I am stuck for hours on trying to calculate time fields. PLEASE DEAR GOD can someone help me. Below is a simple example of my worksheet. A2 (Time IN) = 1:00 PM A3 (Time OUT) = 5:00 PM A4 (Time Worked) = 4:00 (h:mm format) ...

how to calculate seconds elapsed between two moments?
I need the number of seconds between two moments. A1: 14:10:00 A2: 14:15:00 Difference is 5 minutes = 300 seconds. What formula(/cell format) do I need to use to get the number 300 in the result cell? When I use the formula A2-A1 I get a 00:05:00 (in time format) 0.003472222 (in General format) When I use Second(a2-a1) I get 0 how to get 300 ?? Wim Custom format the cell containing the formula to: [ss] The square brackets prevent the seconds from rolling over to minutes and / or hours when they're greater then 60. -- HTH, RD =====================================================...

Adding Barcode at time of sale??
We create our manufacturer pre-barcoded items using a dummy item number which we then replace by wanding the item with the Operations Manager item window open and the dummy # highlighted. If we forget to do that in advance, POS will not recognize the barcode when we sell the item so we have to do an item lookup to find it. Taking time to go into Manager at that point is alittle awkward with the customer waiting. Is there a way to replace the dummy item number in POS, during the sale?? -- WineGuy The Item Properties window in POS is essentially identical to the one in manager. Once yo...

Time format control
Hi everyone, I have a time of 08:38 and would like to truncate the last minute by using formatting to achieve 08:30. I currently do this using a formula to remove the last minute. I have tried custom formatting of hh.m but I still get 08:38. I have even tried hh.m"0" but get 08:380. Any Ideas would be greatly appreciated. Regards Brad You can use this formula, it sounds as though you want round down to nearest 10th minute =FLOOR(A1,1/144) -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Brad kennedy" <brad_6663@hotmail...

IE 6 won't install...
downloaded IE 6 w/outlook express...it gets to 83% installed then I get a message " software doesn't pass logo test for XP ? and won't install? Wrong group .... microsoft.public.windows.inetexploer.ie6_outlookexpress or microsoft.public.windows.inetexploer.ie6.setup or microsoft.public.windows.inetexploer.ie6.browser William "westy" <westy523@comcast.net> wrote in message news:0b5601c368e9$f01c48c0$a001280a@phx.gbl... > downloaded IE 6 w/outlook express...it gets to 83% > installed then I get a message " software doesn't pass > logo test f...

proposed new time issue
we are running office 2007 on exchange 2003 I have one user who whenever he sends out a meeting invite and someone proposes a new time and he accepts the new time it places both the invites on the calendar and doesn't remove the original time. I asked him if he was being prompted to remove the original time and he said no any ideas why this might be happening and how to fix it? Thanks Lynn ...

delete from many tables at the same time
Hi I am new using Access, and this may be an elemental question, but anyway, I have three tables: Customers. Orders. Payments. (Customers' payments) Customers has as primary key the name of the customer and the other two tables have as a primary key an automatic ID integer. How can I configure Access in the way that If I erase a Customer from the table Customers the other two tables will be automatically updated erasing also their Orders and Payments? Thanks, Enrique. -- Eng. Enrique Lopez. Go to the Relationships window and, if not already done, creat...

Convert Time...!convert tenths of a second
How can i convert tenths of a second to time (Hours, min,sec ). Can this happen with macro..? Hi try the following formula =A1/(24*60*60*10) and format the resulting cell as time "Pape" wrote: > How can i convert tenths of a second to time (Hours, min,sec ). Can this > happen with macro..? try custom format [h]:mm:ss.0 if not available introduce it. you will get for e.g. 10:13:12.5 is this what you want Pape <Pape@discussions.microsoft.com> wrote in message news:4102ED26-AD8D-4454-AE81-AA1C8E7C66F1@microsoft.com... > How can i convert tenths of a second to...

Defining X-axis scales as dates or times
I often plot measured data as a scatter plot with the value on the Y-axis, and a time or date on the X-axis. The wizard will pick default values for the X-axis that usually need to be altered. Since moving to Excel 2007 I have been unable to specify the X-axis min and max values as dates or times. With '2003 I used to be able to specify, say, a minimum of 08/09/2008 09:00 and a maximum of 08/09/2008 09:10. Now I would have to use numeric values of 39699.375 and 39699.3819444. Does anyone know a way of doing this? Thanks Hi, That convenient auto conversion does not occur in 2007. Y...

excel Problems #6
While working in excel I am facing two problems 1.after working 1 -2hours in excel ,my excel stops working,even if I am trying to quit by clicking close button it stops responding and the system hangs up.only way to quit the prog is to press cntrl+shift+del and end task.after that I got auto recovery pane that saved my excel work.This happens every day.what's this problem? 2.while inserting a name i.e-insert-name-define-name box-refer to box-I am not able to enter a big formula.after entering say it is around 250 characters cursor not moving forward.Is there any limitation for defi...

Date and time calculations
Hello, I have a problem with a formula in wich I like to add up an amount of hours to a date/time value. Please find below an example of what I am trying to do. Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm) Value A2: 5 (hours) In cel A3 I would like to get the value of A1+A2 (result 16/03/2005 03:00) Can someone suggest what formula I must use? Thanx very much! Robert =a1+(a2/24) Format the way you like. (5 hours is 5/24ths of a day.) Robert wrote: > > Hello, > > I have a problem with a formula in wich I like to add up an amount of > hours to a date/time value. >...

Excel file size increased 3 times after saving!!!!
Hi all, I have a very strange situation with my Excel workbook. I created Excel workbook with conditional formulas (no other formulas) which total size is 3 Mb. After I put simple data for calculation and saved the workbook the size increased to 10Mb! The data itself is just about 70Kb. I use Excel 2002 at work. I should put this workbook on our company server, but our network is not very good and people can't open it because of the 10MB. I have never seen this before! Can somebody help please? Thank you in advance! I don't claim to be an expert on Excel by any...

Time stamp in Excel Sheet
I am looking to entering a value into column A (Data Point), then on column B (Time Stamp), the time that value A was entered. Then another value will be entered into A2 and would like the time that Data Point 2 was entered into B2 and so on. I have tried the IF statement =IF(A1,NOW(),"") in B1,B2, B3... but every time the work sheet reculculates, the Time Stamp column becomes the same. How can I stop this from happening or am I going about this the wrong way. Take a look at http://www.4-consulting.com/News/Ezine_4/Ezine_4_03.htm#t16 "John M" <John M@discussi...

need variable time axis available in XY chart for area chart #3
I am unable to use different X axis in the Area chart, which is available in the X Y charts. How do I use the X Y chart features of variable X axis to make the area chart? ...

importing mail from Outlook Express 6 to Outlook 2002
I'm trying to move Outlook Express 6 emails to another computer that has Outlook 2002, but it's not working. What I did was copy the mail folder with all the .dbx files in it from the old computer to the new comptuer. Then used the import procedure under the File menu. But, it generates the following error message: This mail folder could not be opened. If another application is using this file, please close it and try again. No other app is touching them. I noticed that when choosing the file format to import from, the only option presented for OE was something to th...

how to create a calculated field from another calculated field?
In a pivot table report I would like to add a calculated field that is like the following: = count( columnA ) / count ( columnB) But when I create a calculated field like the above, all I get is a value of 1 in every row of the pivot table. I realize after some experiments, its really performing = count( sum ( columnA ) ) / count( sum ( columnB ) ) So , how do I get what I want? ...

Total of Time
I'm recording the time I spend working on a piece of work in a spreadsheet along with meeting details etc. I've 2 columns that are start and finish times and a third that works out duration. I then want to total the duration into total hours but I can't find a way to do this Any help please TIA Assuming your duration is in column C, just use =SUM(C1:C100) XL's default display will "roll over" every 24 hours. To get the actual total, choose Format/Cells/Number/Custom [h]:mm In article <#NJENCGYEHA.3596@tk2msftngp13.phx.gbl>, "A" <a...

EXCEL FORMULAS #6
I WANT TO SHOW THE ANSWER TO A SUM TO SHOW IN A NONE RELATED CELL.IE:FORMULA SET IN C1=SUM(A1+B2) ANSWER TO SHOW IN D1.CAN THIS BE DONE,IF SO CAN ANYONE SHOW ME HOW. Wyn, Not without using a macro. You can't change a cell with a formula from another cell. You could use =C1 in D1 John "WYN BUNSTON" <anonymous@discussions.microsoft.com> wrote in message news:CEE3C931-8229-44BA-8382-B54D56CF1F64@microsoft.com... > I WANT TO SHOW THE ANSWER TO A SUM TO SHOW IN A NONE RELATED CELL.IE:FORMULA SET IN C1=SUM(A1+B2) ANSWER TO SHOW IN D1.CAN THIS BE DONE,IF SO CAN ANYONE SHOW ...

How do I set a macro to run at specific times?
Hi I have recorded a macro which I want to run automatically every hour. Could someone tell me how I do this You can read about the scheduling procedures at Chip Pearson's site: http://www.cpearson.com/excel/ontime.htm Nick123 wrote: > > Hi > > I have recorded a macro which I want to run automatically every hour. Could > someone tell me how I do this -- Dave Peterson ...

how do I set up my account for the first time?
new company has instructed to "change my outlook settings", but that's it. I know they use the pop3 outgoing, and I know my dsl server. I just don't think I'm doing something right! bibbpromo <bibbpromo@discussions.microsoft.com> wrote: > new company has instructed to "change my outlook settings", but > that's it. I know they use the pop3 outgoing, and I know my dsl > server. I just don't think I'm doing something right! Tell us what version of Outlook you're using, what you've done so far, what doesn't work, and any...

Data Labels #6
On the Chart Options there is an option DATA LABELS. This option lets you print the following VALUE, PERCENT, LABEL and LABEL + PERCENT. Is there any way that you can print the Value and the Label. Or is there anyway you can modify any part of what you want to print beside the section of the pie chart. You can manually change the text within each data label. This is rather tedious, so you can download one of the following Excel add-ins, which allow you to select a worksheet range to use as labels for your points; both are free, easy to install and use. If you want any particluar lab...

how do I import data (distance and time) from MapPoint to Excel?
I have an Excel application which uses distabce and time obtained from MapPoint. Actually I enter those figures manually in my Excel worksheet. Is there way I can import these figures directlt to my Excel cells from MapPoint? Thank you for your answer ...

Changing default Appointment Time from 30 minutes?
Hi everyone, Can anyone tell me how to change the default Appointment Time from 30 minutes to say "0" minutes? I'm running Outlook 2003. Stephen Morgan <kpsm99@gmail.com> wrote: > Can anyone tell me how to change the default Appointment Time from 30 > minutes to say "0" minutes? I'm running Outlook 2003. Do you mean by "default appointment time" to be the time Outlook gives you between the start and end times when you create a new appointment? I believe 30 minutes is the minimum unless the slot for the appointment is preselected. If you...

Amnesty for illegal immigrants will not happen this time.....................
Our politicians lead the league in all talk, no action. Is blowing us some sunshine good enough? Read what The Expert thinks: http://www.ExpertHumor.com/ *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** ...