Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
If i have a start time of 5:00am and a finishing time of 4:25pm, what
function do i use to get the total time of 11 hours & 25 minutes? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
Say you have start time in A1, end time in B1
=B1-A1 will give elapsed time HTH -- AP "Ken Davie" a écrit dans le message de news: ... If i have a start time of 5:00am and a finishing time of 4:25pm, what function do i use to get the total time of 11 hours & 25 minutes? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Ken Davie" wrote in message
u... If i have a start time of 5:00am and a finishing time of 4:25pm, what function do i use to get the total time of 11 hours & 25 minutes? A1 = Start Time A2 = End Time =(A2<A1) * (24 - A1 + A2) + (A2 = A1) * (A2 - A1) Bruno |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
Bruno,
Without meaning to be critical, your formula *appears* to return the correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00 However, if you format the cell as General you will see that the actual value held in the cell is 23.083333333 which is 2 am on January 23 1900. The reason is the use of 24 in your formula. Excel is taking it as 24 days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in: =(A2<A1) * (1- A1 + A2) + (A2 = A1) * (A2 - A1) Shorter ways of doing the same thing: =A2-A1+(A2<A1) or =MOD(A2-A1,1) Neither of which are my original formulas -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Bruno Campanini" wrote in message ... "Ken Davie" wrote in message u... If i have a start time of 5:00am and a finishing time of 4:25pm, what function do i use to get the total time of 11 hours & 25 minutes? A1 = Start Time A2 = End Time =(A2<A1) * (24 - A1 + A2) + (A2 = A1) * (A2 - A1) Bruno |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
That formula works great, but now what function do i use to multiply the
total time by an hourly rate? Ken..... "Sandy Mann" wrote in message ... Bruno, Without meaning to be critical, your formula *appears* to return the correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00 However, if you format the cell as General you will see that the actual value held in the cell is 23.083333333 which is 2 am on January 23 1900. The reason is the use of 24 in your formula. Excel is taking it as 24 days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in: =(A2<A1) * (1- A1 + A2) + (A2 = A1) * (A2 - A1) Shorter ways of doing the same thing: =A2-A1+(A2<A1) or =MOD(A2-A1,1) Neither of which are my original formulas -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Bruno Campanini" wrote in message ... "Ken Davie" wrote in message u... If i have a start time of 5:00am and a finishing time of 4:25pm, what function do i use to get the total time of 11 hours & 25 minutes? A1 = Start Time A2 = End Time =(A2<A1) * (24 - A1 + A2) + (A2 = A1) * (A2 - A1) Bruno |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Ken Davie" wrote in message
u... That formula works great, but now what function do i use to multiply the total time by an hourly rate? It is left as an exercise for the interested reader to work out the number of hours from the number of days. -- David Biddulph |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
=time*24*rate
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ken Davie" wrote in message u... That formula works great, but now what function do i use to multiply the total time by an hourly rate? Ken..... "Sandy Mann" wrote in message ... Bruno, Without meaning to be critical, your formula *appears* to return the correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00 However, if you format the cell as General you will see that the actual value held in the cell is 23.083333333 which is 2 am on January 23 1900. The reason is the use of 24 in your formula. Excel is taking it as 24 days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in: =(A2<A1) * (1- A1 + A2) + (A2 = A1) * (A2 - A1) Shorter ways of doing the same thing: =A2-A1+(A2<A1) or =MOD(A2-A1,1) Neither of which are my original formulas -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Bruno Campanini" wrote in message ... "Ken Davie" wrote in message u... If i have a start time of 5:00am and a finishing time of 4:25pm, what function do i use to get the total time of 11 hours & 25 minutes? A1 = Start Time A2 = End Time =(A2<A1) * (24 - A1 + A2) + (A2 = A1) * (A2 - A1) Bruno |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Sandy Mann" wrote in message
... Bruno, Without meaning to be critical, your formula *appears* to return the correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00 However, if you format the cell as General you will see that the actual value held in the cell is 23.083333333 which is 2 am on January 23 1900. The reason is the use of 24 in your formula. Excel is taking it as 24 days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in: =(A2<A1) * (1- A1 + A2) + (A2 = A1) * (A2 - A1) Shorter ways of doing the same thing: =A2-A1+(A2<A1) or =MOD(A2-A1,1) Neither of which are my original formulas Very interesting formulas, Sandy! Thank you very much Bruno |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Sandy Mann" wrote in message
... Bruno, Without meaning to be critical, your formula *appears* to return the correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00 However, if you format the cell as General you will see that the actual value held in the cell is 23.083333333 which is 2 am on January 23 1900. The reason is the use of 24 in your formula. Excel is taking it as 24 days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in: =(A2<A1) * (1- A1 + A2) + (A2 = A1) * (A2 - A1) Shorter ways of doing the same thing: =A2-A1+(A2<A1) or =MOD(A2-A1,1) Neither of which are my original formulas -- HTH Sandy In Perth, the ancient capital of Scotland Sandy, a friend of mine - Franz Verga - modified =A2-A1+(A2<A1) in =A2-A1+(A2<=A1). As a conseguence whenever you have A1=A2 the formula correctly thinks A2 pertaining to the next day and displays 24:00 instead of Null. Provided you have the cell Custom formatted as [h].mm Bruno |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Bruno Campanini" wrote in message
... Sandy, a friend of mine - Franz Verga - modified =A2-A1+(A2<A1) in =A2-A1+(A2<=A1). As a conseguence whenever you have A1=A2 the formula correctly thinks A2 pertaining to the next day and displays 24:00 instead of Null. Provided you have the cell Custom formatted as [h].mm Thanks for sharing that Bruno, It also returns 24:00 if both cells are blank, if that is a problem then you could add another test: =A2-A1+(A2<=A1)*(A1<"") -- Regards, Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding time
"Sandy Mann" wrote in message
... Thanks for sharing that Bruno, It also returns 24:00 if both cells are blank, if that is a problem then you could add another test: =A2-A1+(A2<=A1)*(A1<"") Or =(A2-A1+(A2<=A1))*AND(A1<"",A2<"") Ciao Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is the formula for adding up time intervals? | Excel Discussion (Misc queries) | |||
Help with Adding Time Hrs and Mins | Excel Discussion (Misc queries) | |||
Adding time | New Users to Excel | |||
adding in time formats | Excel Discussion (Misc queries) | |||
Adding time to multiple cells using TIMECODE | Excel Discussion (Misc queries) |