Working with Time in formulas/calculations

[Braunn]

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?

 

[Braunn]

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?

 

[Ron Rosenfeld]

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