Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
Hi, I been working on this timesheet and I thought I had it right. I was
working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest ¼ hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
On Thu, 2 Mar 2006 08:25:25 -0800, "John"
wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C1 5-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. Just a small question first. How do you get only 3.75 hours from 8.00 am to 11.45 pm? Surely that's an elapsed time of 15.75 hours? Or did you mean 8.00 pm to 11.45 pm? Rgds Richard Buttrey __ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
Sorry, I wrote this in word. This site has no preview before post or edit
after post. The layout is IN OUT Meal IN OUT Total The examples: IN OUT Meal IN OUT Total 8:07 AM 11:52 AM 3.75 IN OUT Meal IN Out Total 8:07 AM 11:52 AM 1:12 PM 4:58 PM 7.50 Thanks "John" wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest ¼ hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
Sorry I don't have english version of Excel
In french version, I put the following formula in C6: =ARRONDI.AU.MULTIPLE(B6;1/24/4)-ARRONDI.AU.MULTIPLE(A6;1/24/4) This rounds up numbers to the nearest multiple of 2nd argument The function needs FUNCRES.XLA (Utilitaire d'Analyse) HTH -- AP "John" a crit dans le message de ... Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24) )+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2) The "Meal" cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
IF I understand you correctly, i.e.:
B C D E F G 1 IN OUT MEAL IN OUT TOTAL 2 8:07 11:52 12:29 16:45 Then G2 =(ROUND(MOD(C2-B2,1)*96,0)+ROUND(MOD(F2-E2,1)*96,0))/4 In article , "John" wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest 14 hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+ IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
Sorry That is 11:52 AM
"Richard Buttrey" wrote: On Thu, 2 Mar 2006 08:25:25 -0800, "John" wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest ¼ hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C1 5-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. Just a small question first. How do you get only 3.75 hours from 8.00 am to 11.45 pm? Surely that's an elapsed time of 15.75 hours? Or did you mean 8.00 pm to 11.45 pm? Rgds Richard Buttrey __ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
=(A2<"")*(B2<"")*(24*(B2<A2)+(ROUND(B2*96,0)-ROUND(A2*96,0))/4)+(D2<"")*(E2<"")*(24*(E2<D2)+(ROUND(E2*96,0)-ROUND(D2*96,0))/4)
Format the total column as general. This formula takes into account times that go through midnight, and does not subtract the cells unless both have a value. "John" wrote: Sorry, I wrote this in word. This site has no preview before post or edit after post. The layout is IN OUT Meal IN OUT Total The examples: IN OUT Meal IN OUT Total 8:07 AM 11:52 AM 3.75 IN OUT Meal IN Out Total 8:07 AM 11:52 AM 1:12 PM 4:58 PM 7.50 Thanks "John" wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest ¼ hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet Help. Rounding to the nearest 1/4
Thank you:). Eaxactly what I was looking for.
"Sloth" wrote: =(A2<"")*(B2<"")*(24*(B2<A2)+(ROUND(B2*96,0)-ROUND(A2*96,0))/4)+(D2<"")*(E2<"")*(24*(E2<D2)+(ROUND(E2*96,0)-ROUND(D2*96,0))/4) Format the total column as general. This formula takes into account times that go through midnight, and does not subtract the cells unless both have a value. "John" wrote: Sorry, I wrote this in word. This site has no preview before post or edit after post. The layout is IN OUT Meal IN OUT Total The examples: IN OUT Meal IN OUT Total 8:07 AM 11:52 AM 3.75 IN OUT Meal IN Out Total 8:07 AM 11:52 AM 1:12 PM 4:58 PM 7.50 Thanks "John" wrote: Hi, I been working on this timesheet and I thought I had it right. I was working on actual time worked but this is not what my employers want. I am hope you can help. My timesheet has a layout like is: IN OUT Meal IN OUT Total What we need is to have the time round to the nearest ¼ hour within each IN and OUT. Example: IN OUT 8:07 AM 11:52 PM The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with the output being 3.75 hours. This would need to be done within the formula (?) and not change the cells format that the times have been enter but showing the end results in the TOTAL cell. Example: IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 3.75 As you see there is still another IN/OUT. I would need to do the same with those. Now the way I currently have it setup is when I enter the time in the IN cells I get no output until I enter the time in the OUT cell. The formula I am currently using is below. =ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),(( F15-E15)*24)+24,(F15-E15)*24)),2) The Meal cell (D15) has no value. It is just for cosmetic. The end result should look like this. IN OUT Meal IN OUT Total 8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50 Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding to nearest hundred dollar in Excel | New Users to Excel | |||
Rounding numbers to the nearest 5 or 0 | Excel Worksheet Functions | |||
Rounding up to the nearest nickel | Excel Discussion (Misc queries) | |||
Rounding to nearest integer | Excel Worksheet Functions | |||
Rounding numbers to the nearest thousand | Excel Discussion (Misc queries) |