How to customize =Now()

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

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.
 
Now is a function. Functions return values. They do not affect the format of
the cell that they are placed in. If you want a different format you need to
format the cell you put the function in.
 
Right-click on the cell, choose Format Cells...>Number>Custom

In the Type: box, enter:
dd/mm/yy hh:mm:ss

If you want am/pm instead of military time, use:
dd/mm/yy hh:mm:ss am/pm

If you have an elapsed time, use:
hh:mm:ss

If your elapsed time goes over 24 hours, you can use:
[hh]:mm:ss

If you want to display number of days elapsed, as well as time, use:
dd hh:mm:ss am/pm

All of these options are explained in Custom Format in Help.

Regards,
Fred
 
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
 
Thank you, that has really helped me.

Jim Thomlinson said:
Now is a function. Functions return values. They do not affect the format of
the cell that they are placed in. If you want a different format you need to
format the cell you put the function in.
Click to expand...
 
Fred you have helpped me learn to format the cells however. 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.

Robin
Fred Smith said:
Right-click on the cell, choose Format Cells...>Number>Custom

In the Type: box, enter:
dd/mm/yy hh:mm:ss

If you want am/pm instead of military time, use:
dd/mm/yy hh:mm:ss am/pm

If you have an elapsed time, use:
hh:mm:ss

If your elapsed time goes over 24 hours, you can use:
[hh]:mm:ss

If you want to display number of days elapsed, as well as time, use:
dd hh:mm:ss am/pm

All of these options are explained in Custom Format in Help.

Regards,
Fred

Robin said:
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.
Click to expand...
Click to expand...
 
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.
 
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
 
I should have pointed out that the 01/14/00 in your example is 14th
Jan 1900 (sorry, I put 1990 in my earlier post). This is the 14 days
difference, but Excel is formatting it as a date, so 14 days after the
reference date is 14th Jan 1900, and your format is not showing the
19.

Hope this helps.

Pete
 
Pete,

I really like the instruction. I was thinking it had to be a specific way
with the dd/mm/yy; however this would be easier and a more accurate count of
time in that months vary in days and Years vary because of leap year. Your
formula is a more accurate measurement the is the most inclusive. It works
and subtracts appropriatly. I thank everyone that has tried to help me. It is
learning something new, a new process and then realizing what is actually
happening.

Robin
 
You can't use a date format like mm/dd/yy to display elapsed times, because
there's no such thing as month 0. If your elapsed days are less than 31, you
can use a format of:
dd hh:mm:ss

Unfortunately, Excel will not display more than 31 days. If you will have
more than 31 days elapsed, you need to use a different technique, which was
explained to you in a previous post.

Regards,
Fred

Robin said:
Fred you have helpped me learn to format the cells however. 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.

Robin
Fred Smith said:
Right-click on the cell, choose Format Cells...>Number>Custom

In the Type: box, enter:
dd/mm/yy hh:mm:ss

If you want am/pm instead of military time, use:
dd/mm/yy hh:mm:ss am/pm

If you have an elapsed time, use:
hh:mm:ss

If your elapsed time goes over 24 hours, you can use:
[hh]:mm:ss

If you want to display number of days elapsed, as well as time, use:
dd hh:mm:ss am/pm

All of these options are explained in Custom Format in Help.

Regards,
Fred

Robin said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Well, thanks for feeding back, Robin - glad to be of help.

Note Fred's recent response to you, however.

Pete
 
Back
Top