How to customize =Now()
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. |
How to customize =Now()
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. -- HTH... Jim Thomlinson "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. |
How to customize =Now()
Right-click on the cell, choose Format Cells...NumberCustom
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" wrote in message ... 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. |
How to customize =Now()
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. |
How to customize =Now()
Thank you, that has really helped me.
"Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "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. |
How to customize =Now()
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" wrote: Right-click on the cell, choose Format Cells...NumberCustom 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" wrote in message ... 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. |
How to customize =Now()
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. |
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 - |
How to customize =Now()
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 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 - |
How to customize =Now()
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 "Pete_UK" wrote: 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 - |
How to customize =Now()
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" wrote in message ... 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" wrote: Right-click on the cell, choose Format Cells...NumberCustom 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" wrote in message ... 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. |
How to customize =Now()
Well, thanks for feeding back, Robin - glad to be of help.
Note Fred's recent response to you, however. Pete On Aug 15, 10:55*pm, Robin wrote: 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 "Pete_UK" wrote: 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 -- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com