How to customize =Now()
Dates and times are just numbers to Excel, so you can subtract them.
However, Excel uses integers to stand for dates (they are the number
of days since a reference data of 1st Jan 1990), and fractions of a 24-
hour day to stand for times. You can format the cell differently so
that the elapsed days are not interpreted as a date. For example, use
this custom format on the cell which contains the subtraction formula:
d" days "hh:mm:ss
This will give you something like:
14 days 09:18:20
depending on the value of NOW() in your region.
Hope this helps.
Pete
On Aug 15, 7:12*pm, Robin wrote:
I have not worked with macros. I do have a static start date that will be
typed by the user; however it is less than a month and is returning a month
has elapsed. I know that excell only gives examples of elapsed time in hours
and seconds. Is there anyway to accomplish this for date and time for
mm/dd/yy hh:mm:ss - mm/dd/yy hh:mm:ss. The times that I have are Current,
Start, End. I would like to show the elapsed time of *Current minus Start and
End minus Start. Is this possible? Please notice the following example that I
have given. It does not work appropriatly. It is important that be able to
show elapsed time via date and time.
I am still having a problem in that I want the time to be all inclusive. It
is not subtracting properly. for instance
*08/15/09 *12:56:15
-08/01/09 *11:00:50
=01/14/00 *01:55:25
*The 1 should not be in the month since a month has not elapsed.
"Gord Dibben" wrote:
Problem with NOW() is its volatility.
To calculate elapsed time you would need a staic start time.
I would use a macro to enter a static time.
Sub NOWTIME()
* * ActiveCell.Value = Format(Now, "dd/mm/yyyy h:mm:ss")
End Sub
Without the date format.
Sub NOWTIME()
* * ActiveCell.Value = Format(Now, "h:mm:ss")
End Sub
Gord Dibben *MS Excel MVP
On Fri, 14 Aug 2009 15:56:02 -0700, Robin
wrote:
How do you customize the =Now() so that it is in the format of dd/mm/yy and
hh:mm:ss instead of dd/mm/yyyy and hh:mm (military time). I am trying to have
an elapsed time with the now time minus the start time which is in the form
of mm/dd/yy hh:mm:ss. Will it give the correct elapsed time if it is in the
current format.- Hide quoted text -
- Show quoted text -
|