Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pasting a date or hour in a cell 1
- Thread starter MrClinker
- Start date
SkipVought
Programmer
Hi,
Just macro record
Copy/Paste Special - Values
Skip,
Skip@TheOfficeExperts.com
Just macro record
Copy/Paste Special - Values
Skip,
Skip@TheOfficeExperts.com
SkipVought
Programmer
Actually, It's NOT in a text format -- It's in a Date/Time format
But you probably has
=NOW()
in your cell -- thats what makes the Date/Time dynamic.
All Date/Time values are stored as numbers like the Date/time right now is...
37868.6481980324
which can be formatted in Format/Cells/Number and select a Date format that includes time and you coud get...
9/4/03 3:33 PM
Skip,
Skip@TheOfficeExperts.com
But you probably has
=NOW()
in your cell -- thats what makes the Date/Time dynamic.
All Date/Time values are stored as numbers like the Date/time right now is...
37868.6481980324
which can be formatted in Format/Cells/Number and select a Date format that includes time and you coud get...
9/4/03 3:33 PM
Skip,
Skip@TheOfficeExperts.com
-
1
- #4
If you want to return ANY number, date or time as text, then use the TEXT() function:
=TEXT(A1,"hh:mm"
That will return the Time that is in cell A1 as text if it is "viewed" as 14:00 or as 0.583333333333333 (which is the TimeSerial for 14:00)
I hope this helps!
Peace!![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
Mike
Never say Never!!!
Nothing is impossible!!!
=TEXT(A1,"hh:mm"
That will return the Time that is in cell A1 as text if it is "viewed" as 14:00 or as 0.583333333333333 (which is the TimeSerial for 14:00)
I hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
DaleWatson123321
Programmer
Here's an "assist"...
Skip was right about the need to use: Edit - Paste Special - Values ... to convert the formula to a "fixed" value.
While Skip's formatting suggestions are accurate, when copying a formatted date formula and converting it to a value, the result will still be a value - like the one Skip already provided... 37868.6481980324. And I'm sure it's not a NUMBER that you want.
Here are a couple of formulas as options. When they are copied and converted to values, they will stay fixed as TEXT.
1) =TEXT(NOW(),"mmmm dd, yyyy"
2) =TEXT(NOW(),"mmmm dd, yyyy - hh:mm"
A problem with the hour:minutes above, is that it's military time - i.e. 24 hour clock. Thus, the only way the end-user can be assured the hour:minutes is based on military time, is if the hour:minutes is beyond 13:00.
With any hour:minutes up to 13:00, the user could interpret this to be EITHER AM or PM, for example 12:35 AM or 12:35 PM.
3) While the following formulas are rather "lenghty", they do afford some flexibility. I normally place the formula on a SEPARATE page, and then reference it via formula.
3a) This one displays the hours:minutes, followed by "a.m." or "p.m.", followed by the Month, Day and Year.
=" "&RIGHT(" "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m."&" "&CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)
3b) This one is the same as above, execpt that the hour:minutes come AFTER the Month, Day and Year.
=CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)&" "&RIGHT(" "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m."
When I place the formula on a SEPARATE sheet, I assign a range name to the formula - e.g. "date_". Then, when I reference the formula at the top of a report, for example, I will use a formula like: ="Printed: "&date_
I hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Skip was right about the need to use: Edit - Paste Special - Values ... to convert the formula to a "fixed" value.
While Skip's formatting suggestions are accurate, when copying a formatted date formula and converting it to a value, the result will still be a value - like the one Skip already provided... 37868.6481980324. And I'm sure it's not a NUMBER that you want.
Here are a couple of formulas as options. When they are copied and converted to values, they will stay fixed as TEXT.
1) =TEXT(NOW(),"mmmm dd, yyyy"
2) =TEXT(NOW(),"mmmm dd, yyyy - hh:mm"
A problem with the hour:minutes above, is that it's military time - i.e. 24 hour clock. Thus, the only way the end-user can be assured the hour:minutes is based on military time, is if the hour:minutes is beyond 13:00.
With any hour:minutes up to 13:00, the user could interpret this to be EITHER AM or PM, for example 12:35 AM or 12:35 PM.
3) While the following formulas are rather "lenghty", they do afford some flexibility. I normally place the formula on a SEPARATE page, and then reference it via formula.
3a) This one displays the hours:minutes, followed by "a.m." or "p.m.", followed by the Month, Day and Year.
=" "&RIGHT(" "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m."
&" "&CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)3b) This one is the same as above, execpt that the hour:minutes come AFTER the Month, Day and Year.
=CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December"
&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)&" "&RIGHT(" "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m."When I place the formula on a SEPARATE sheet, I assign a range name to the formula - e.g. "date_". Then, when I reference the formula at the top of a report, for example, I will use a formula like: ="Printed: "&date_
I hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Sorry Dale but I have to disagree,
=TEXT(NOW(),"hh:mm" will return the time (as both of us have already posted.
BUT
To get AM/PM you could simply use this:
=TEXT(NOW(),"[$-409]h:mm AM/PM;@"
and if you want to have the Month, Day and Year
After the AM/PM time: =TEXT(NOW(),"[$-409]h:mm AM/PM;@"&" "&TEXT(NOW(),"mmmm d, yyyy"
Before the AM/PM time: =TEXT(NOW(),"mmmm d, yyyy"&" "&TEXT(NOW(),"[$-409]h:mm AM/PM;@"
Now you can change your Named Ranges.
Gerardo: I hope that these posts have helped you solve your problem!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
=TEXT(NOW(),"hh:mm"
will return the time (as both of us have already posted.BUT
To get AM/PM you could simply use this:
=TEXT(NOW(),"[$-409]h:mm AM/PM;@"
and if you want to have the Month, Day and Year
After the AM/PM time: =TEXT(NOW(),"[$-409]h:mm AM/PM;@"
&" "&TEXT(NOW(),"mmmm d, yyyy"Before the AM/PM time: =TEXT(NOW(),"mmmm d, yyyy"
&" "&TEXT(NOW(),"[$-409]h:mm AM/PM;@"Now you can change your Named Ranges.
Gerardo: I hope that these posts have helped you solve your problem!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
DaleWatson123321
Programmer
Mike,
I would have recognized your first posting (as being a solution), but I noticed MUCH later (after I got home from work) that we had posted at the SAME time.
Your second posting is deserving of a STAR, so here's one from me.
A couple of points/observations, though...
1) Removing the "@" seems NOT to make any difference in the result. Can you explain the purpose of the "@" ?
2) A scenario... Your boss insists that for the sake of maintaining a long-standing "layout convention" for displaying the date and time, the "AM" and "PM" MUST be display as "a.m." and "p.m.". This actually happened to me years ago, and thus my creation of the custom formula.
If you can offer a means of having Excel display "a.m." and "p.m.", then I could be persuaded to "throw out my formula".
3) Another situation arose awhile back, where there was a need to display the months in FRENCH, and of course some of the months have accents (e.g. "Février" for February). (This of course was on a PC with English as the language used normally.) With my formula being "open-ended" in terms of what is displayed for the months, this was not a problem.
4) The reason for the named range for the date, and having the date on a separate sheet... is "ONLY" because Microsoft has configured Excel (for some STRANGE reason) to ALWAYS display the ENTIRE formula on the Formula Bar whenever the cursor is placed on a formula. Therefore, with ANY medium or large formula, the result is that MUCH of the screen is BLOCKED FROM VIEW each time the user places the cursor on such a formula. I know the Formula Bar can be turned OFF and then ON, but that can be a "pain", and most users prefer to have the Formula Bar on ALL the time.
Alternatively, Microsoft should begin using the example of Lotus 123, where the Formula Bar ONLY displays the entire formula AFTER the user hits the "EDIT" key <F2>.
There... that's the "reason for my insanity" in continuing to use "my old formula".
Thanks for this contribution, Mike, and your others; they're appreciated.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I would have recognized your first posting (as being a solution), but I noticed MUCH later (after I got home from work) that we had posted at the SAME time.
Your second posting is deserving of a STAR, so here's one from me.
A couple of points/observations, though...
1) Removing the "@" seems NOT to make any difference in the result. Can you explain the purpose of the "@" ?
2) A scenario... Your boss insists that for the sake of maintaining a long-standing "layout convention" for displaying the date and time, the "AM" and "PM" MUST be display as "a.m." and "p.m.". This actually happened to me years ago, and thus my creation of the custom formula.
If you can offer a means of having Excel display "a.m." and "p.m.", then I could be persuaded to "throw out my formula".
3) Another situation arose awhile back, where there was a need to display the months in FRENCH, and of course some of the months have accents (e.g. "Février" for February). (This of course was on a PC with English as the language used normally.) With my formula being "open-ended" in terms of what is displayed for the months, this was not a problem.
4) The reason for the named range for the date, and having the date on a separate sheet... is "ONLY" because Microsoft has configured Excel (for some STRANGE reason) to ALWAYS display the ENTIRE formula on the Formula Bar whenever the cursor is placed on a formula. Therefore, with ANY medium or large formula, the result is that MUCH of the screen is BLOCKED FROM VIEW each time the user places the cursor on such a formula. I know the Formula Bar can be turned OFF and then ON, but that can be a "pain", and most users prefer to have the Formula Bar on ALL the time.
Alternatively, Microsoft should begin using the example of Lotus 123, where the Formula Bar ONLY displays the entire formula AFTER the user hits the "EDIT" key <F2>.
There... that's the "reason for my insanity" in continuing to use "my old formula".
Thanks for this contribution, Mike, and your others; they're appreciated.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #8
Please note I wrote "function" in my question. I know that using COPY/Paste Special - Values works. But I want to enter data in a cell and automatically add in another cell the hour that the data was entered, without running any macro, and, of course, I want this hour not to change.
Thanks,
Gerardo.
Thanks,
Gerardo.
- Thread starter
- #9
SkipVought
Programmer
You can use the Worksheet_Change event to run your macro...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgnMine as Range
Set rngMine = Application.Intersect(Target, Range("A1"))
If Not rngMine is Nothing Then YouMacro
End Sub
[code]
Hope this helps :-)
Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]- Thread starter
- #11
Enter the follwing formula into the cell next to the date (in my example cell A1, so you would enter the following formula into cell B1).
The formula will cause a Circular reference to cell B1 sou you have to allow Iteration. To do this go to Tools->Options... and in the Calculation tab check the box Allow Iteration. Set Maximum Iteration to 1 and Maximum Change to 0.
Here is the formula:
=IF(ISBLANK(A1),"",IF(B1="",NOW(),B1))
Copy this formula down the range where you want the "date stamp". It will enter the date and time into cell B1 when data is entered into cell A1 for the first time (unfortunately, not when it changes).
I hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
The formula will cause a Circular reference to cell B1 sou you have to allow Iteration. To do this go to Tools->Options... and in the Calculation tab check the box Allow Iteration. Set Maximum Iteration to 1 and Maximum Change to 0.
Here is the formula:
=IF(ISBLANK(A1),"",IF(B1="",NOW(),B1))
Copy this formula down the range where you want the "date stamp". It will enter the date and time into cell B1 when data is entered into cell A1 for the first time (unfortunately, not when it changes).
I hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
Another (VBA solution) for what you want to do (that will work whenever you add, delete or change data in a cell):
Enter this into the Worksheet Object Module where you want to have this happen:
I hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
Enter this into the Worksheet Object Module where you want to have this happen:
Code:
Option Explicit
Dim TimeStamp As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Cells(TimeStamp.Row, TimeStamp.Column + 1) = Format(Now, "hh:mm")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set TimeStamp = Target
End SubI hope this helps!
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
- Status
Similar threads
- Locked
- Question
- Locked
- Question