How to customize =Now()

[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.

 

[Jim Thomlinson]

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.

 

[Fred Smith]

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

 

[Gord Dibben]

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

 

[Robin]

Thank you, that has really helped me.

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.

 

[Robin]

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

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

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.

 

[Robin]

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.

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Robin]

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

 

[Fred Smith]

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

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

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

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.

 

[Pete_UK]

Well, thanks for feeding back, Robin - glad to be of help.

Note Fred's recent response to you, however.

Pete