Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with Time in formulas/calculations
Let's say I have a construction budget figure in cell A1 ($308,239,413).
A2 contains an hourly income ($4,930,100). A3 then tells me how many hours I need to meet my budget (A1/A2). Except that, if I format A3 to [h]:00:00 it shows 1500:31:36 for what should be roughly 63 hours. It appears that the "63" answer is being treated as days such that if A3 is changed to A1/A2/24, I get the appropriate 62:31:19. This seems like a work around rather than the correct approach to get the solution I want to see. Any advice? Next, I want to be able to then take that result (in hours) and add it to the current time to show a target date/time for completion. However, since the income is paid hourly, on the hour, I want the target to reflect whole hour increments. i.e. When I set A5 to Now() and A6 to A5+A3, I get 3:39 am. However, since payments come on the hour, not throughout, it would need to say 4am. Is there a way to round A3 to a whole hour? And, is there a way for me to manually update the time of a Now function while retaining the date already shown? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with Time in formulas/calculations
Okay, I changed the format of A3 to general and rounded the formula to a
whole number. I then changed A6 to A5+(A3/24). So I now get whole hours added to the current date/time shown in A5. Is there a way to "round" the Now() function so that the result is the nearest whole hour (or portion thereof, I'm specifically looking for 7 minutes after the hour) that has already passed? So that at 1:07pm it will update to 1:07pm, but will continue to reflect that date/time until it updates again at 2:07pm? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with Time in formulas/calculations
On Sat, 15 Nov 2008 11:17:01 -0800, Braunn
wrote: Let's say I have a construction budget figure in cell A1 ($308,239,413). A2 contains an hourly income ($4,930,100). A3 then tells me how many hours I need to meet my budget (A1/A2). Except that, if I format A3 to [h]:00:00 it shows 1500:31:36 for what should be roughly 63 hours. It appears that the "63" answer is being treated as days such that if A3 is changed to A1/A2/24, I get the appropriate 62:31:19. This seems like a work around rather than the correct approach to get the solution I want to see. Any advice? Yours is a correct approach. Excel stores time as days and fractions of days. So if your units are in hours, you must divide by 24. Next, I want to be able to then take that result (in hours) and add it to the current time to show a target date/time for completion. However, since the income is paid hourly, on the hour, I want the target to reflect whole hour increments. i.e. When I set A5 to Now() and A6 to A5+A3, I get 3:39 am. However, since payments come on the hour, not throughout, it would need to say 4am. Is there a way to round A3 to a whole hour? =round(a3*24,0)/24 or in Excel 2007 or with the Analysis Tool Pak installed in earlier versions: =mround(a3,time(1,,)) If you always want to round up ( or round down) you could use the CEILING or FLOOR functions: =ceiling(a3,1/24) Note that 1/24 is the same as time(1,,) And, is there a way for me to manually update the time of a Now function while retaining the date already shown? Do you mean like keep yesterday's date but today's time? Or something else? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations that were working are now not. | Excel Discussion (Misc queries) | |||
calculations not working | Excel Worksheet Functions | |||
Formulas stop working on all excell spreads at the same time | Excel Worksheet Functions | |||
calculations quit working | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |