If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
to find diffrence in time
Hi all,
Please anybody can help me to write a general function that can be used in excel as well as outside excel. I have the following information opening time closing time breake Effective hrs 9.00 17.30 1.40 ? 8.30 16.40 1.50 ? 7.40 17.50 2.45 ? I want to write a function to find effective hours. Pls help With thanks Polachan |
#2
|
|||
|
|||
to find diffrence in time
Hi,
I have no idea what you mean by a formula that will work outside Excel but this will work in Excel =((B1-A1)-C1)*24 Mike "pol" wrote: Hi all, Please anybody can help me to write a general function that can be used in excel as well as outside excel. I have the following information opening time closing time breake Effective hrs 9.00 17.30 1.40 ? 8.30 16.40 1.50 ? 7.40 17.50 2.45 ? I want to write a function to find effective hours. Pls help With thanks Polachan |
#3
|
|||
|
|||
to find diffrence in time
On Dec 8, 9:06*pm, pol wrote:
Hi all, Please anybody can help me to write a general function that can be used in excel as well as outside excel. I have the following information opening time * * * * closing time * *breake * * * * Effective hrs 9.00 * * * * * * * * * * *17.30 * * * * * *1.40 * * * * * *? 8.30 * * * * * * * * * * *16.40 * * * * * *1.50 * * * * * *? 7.40 * * * * * * * * * * *17.50 * * * * * *2.45 * * * * * *? I want to write a function to find effective hours. Pls help With thanks Polachan Pol, First ..... background: Excel stores dates as the number of days from either jan 1 1904 or jan 1 1900, depending on setup. Assume you, like most people are using the 1900 setup. 1/1/1900 is day 1, 2/1/190 is day 2, 1/1/1901is day 366 and so on. These day values are called date serials. Times are stored as a fraction of a day, i.e noon is .5, 6pm is .75 8am is, .333333. so 0900 is .375. To enter times, use the ":" separator, and Excel will automatically recognise the entry as a time value. This makes date/time calculations really easy, with one drawback a date/time value can never be negative. Assume your data is columns A, B and C, with the formula in D, first data row at row 2. Cell D2 contains the formula "=B2-A2-C2" and displays the result "6:50" (remember to use the : separator. Copy down. This will only work if the times don't go over midnight. If that is the case, you need to use a slightly more complicated version, and incorporate the IF function. try "=If( A2B2, 1+B2-A2-C2, B2-A2-C2)" the if function has three parts: Logical Test (in this case A2B2) - the Logical test must evaluate to either true or false Value if true (1+B2-A2-C2) - this is the formula for when the logical test = true Value if false (the rest) - when logical test = false Each part is separated by the "," In this If function if A2 is greater than B2 (start is after finish) then we add one to the formula to calculate the time. Assume start 21:00, finish 03:00, elapsed time 6:00, however Excel would calculate this as .125 (B2) - .875 (A2) and end up with negative .75 BUT Excel cannot recognise the negative time, and therefore produces an error message. By adding 1 to the formula, we effectively make the calculation 1.125-. 875 result .25 displayed as "6:00" HTH Steve |
#4
|
|||
|
|||
to find diffrence in time
Hi Pol
If you use a colon separator for standard time notation (ex. 9:00) rather than a decimal point (9.00) the following will give you a result in a HH:MM format: =B1-A1-C1 With cell A containing the start time, B the finish time and C the break time. Start Finish Break Hours 9:00 17:30 1:40 6:50 Note: If the start time is before midnight and the finish time is after, 14:00 to 1:00 (AM), the finish time must be entered as-in the example-25:00. Mike's formula results in a decimal number for easy calculation when multiplied by hourly compensation, but you still need to use the colon format (9:00) to make it work. "Mike H" wrote: Hi, I have no idea what you mean by a formula that will work outside Excel but this will work in Excel =((B1-A1)-C1)*24 Mike "pol" wrote: Hi all, Please anybody can help me to write a general function that can be used in excel as well as outside excel. I have the following information opening time closing time breake Effective hrs 9.00 17.30 1.40 ? 8.30 16.40 1.50 ? 7.40 17.50 2.45 ? I want to write a function to find effective hours. Pls help With thanks Polachan |
Thread Tools | |
Display Modes | |
|
|