Excel automatically changes the formatting of the cell to "Time"

Hello, 

I have a long column of numbers [dates in the YY:DD format].  I wanted to 
replace
":61" to ":59".  Even though the cells are initially formatted as Text, as 
soon as I make the change, Excel changes the formatting to Time, and the cell 
with the change now has text ":59:00" in it.  

Is it possible to force Excel to keep the cells formatted as Text?

Thank you!
0
SAM4137 (239)
7/24/2005 12:17:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 3

Sam,

If the cells are truly formatted as text (Format - Cells - Number - Text), 
the formatting should never change, and you should always see exactly what 
you've typed.  Give us an example of a cell before you've made the change, 
and tell us what Format - Cells - Number tab indicates.
--
Earl Kiosterud
www.smokeylake

"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:FD363A5A-6BA8-4E34-A178-7F123AF605D1@microsoft.com...
> Hello,
>
> I have a long column of numbers [dates in the YY:DD format].  I wanted to
> replace
> ":61" to ":59".  Even though the cells are initially formatted as Text, as
> soon as I make the change, Excel changes the formatting to Time, and the 
> cell
> with the change now has text ":59:00" in it.
>
> Is it possible to force Excel to keep the cells formatted as Text?
>
> Thank you! 


0
someone798 (944)
7/24/2005 5:13:59 PM
When you do an Edit|Replace, excel will "help" you and change the value to time
and it helped changing the cell's format to custom, too ([h]:mm:ss).  (Well, it
did for me in xl2003).

Manually typing the new entry didn't do it.  Edit|Replace was too helpful.



Earl Kiosterud wrote:
> 
> Sam,
> 
> If the cells are truly formatted as text (Format - Cells - Number - Text),
> the formatting should never change, and you should always see exactly what
> you've typed.  Give us an example of a cell before you've made the change,
> and tell us what Format - Cells - Number tab indicates.
> --
> Earl Kiosterud
> www.smokeylake
> 
> "Sam" <Sam@discussions.microsoft.com> wrote in message
> news:FD363A5A-6BA8-4E34-A178-7F123AF605D1@microsoft.com...
> > Hello,
> >
> > I have a long column of numbers [dates in the YY:DD format].  I wanted to
> > replace
> > ":61" to ":59".  Even though the cells are initially formatted as Text, as
> > soon as I make the change, Excel changes the formatting to Time, and the
> > cell
> > with the change now has text ":59:00" in it.
> >
> > Is it possible to force Excel to keep the cells formatted as Text?
> >
> > Thank you!

-- 

Dave Peterson
0
petersod (12005)
7/24/2005 7:44:41 PM
I think you could use a column of helper cells:

=substitute(a1,":61",":59")
(and dragdown)

Then edit|copy
and edit|Paste special|Values over the original range
(then delete that column of helper cells)

Or you could select the range and then run a macro:

Option Explicit
Sub testme()

    Dim myCell As Range
    Dim myRng As Range
    Dim fStr As String
    Dim tStr As String
    
    fStr = ":59"
    tStr = ":61"
    
    Set myRng = Nothing
    On Error Resume Next
    Set myRng = Intersect(Selection, _
                  Selection.Cells.SpecialCells _
                      (xlCellTypeConstants, xlTextValues)).Cells
    On Error GoTo 0
    
    If myRng Is Nothing Then
        MsgBox "Please select some cells with values!"
        Exit Sub
    End If
    
    For Each myCell In myRng.Cells
        With myCell
            .Value = Application.Substitute(.Value, fStr, tStr)
        End With
    Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sam wrote:
> 
> Hello,
> 
> I have a long column of numbers [dates in the YY:DD format].  I wanted to
> replace
> ":61" to ":59".  Even though the cells are initially formatted as Text, as
> soon as I make the change, Excel changes the formatting to Time, and the cell
> with the change now has text ":59:00" in it.
> 
> Is it possible to force Excel to keep the cells formatted as Text?
> 
> Thank you!

-- 

Dave Peterson
0
petersod (12005)
7/24/2005 7:51:56 PM
Reply:

Similar Artilces:

Timed Rental
Retail Management Systems Ltd. has just released a new addon for Timed Rental. RMS TimeZone allows you to rent out 'zones'. The easy thing is, you decide what a zone is. Whether you own a golf dome, billiard hall, shooting range (see list below for many other industries) you have the power to specify base pricing, minimum times, and additional per minute pricing. The visual interface tells the employees which zones are occupied/rented, by which customer, and how long the zone has been in use. TimeZone supports an unlimited number of zones. This add-on provides easy to use mouse...

Time differances
1 have 4 boxes start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks Trever, =(B3-A3)*24-C3*24 Format for General, Number, or anything except Date/Time. Format - Cells - Number. You'll get 7.5. If you got 7:50, as you requested, it would look like hours and minutes. -- Earl Kiosterud www.smokeylake.com "Trever B" <TreverB@discussions.microsoft.c...

how do i add time up in exel
everytime i use =sum(a1:a30)*24 i keep getting an odd number even though i only enter even numbers ,this is to add my hours worked in a month Tell us what's in a1:a30. If they are all even numbers, you should definitely get an even result. But if they include minutes, you can get an odd number of hours. -- Regards, Fred Please reply to newsgroup, not e-mail "loadie" <loadie@discussions.microsoft.com> wrote in message news:12306C49-050C-4DED-AD75-2ECEE54A24F7@microsoft.com... > everytime i use =sum(a1:a30)*24 i keep getting an odd number even though > i &g...

Amount of time between dates
Hello all, My last post gave me a super quick answer to what I couldn't figure out after days and days of personal searching and trial and error. What a great user group! Got another question that I hope is fairly straight forward, but it is a new area to me, dealing with dates. Basically, the very first column contains dates. To be precise, they are the dates of stock market transactions. For the past six months, I have been estimating that there have been 252 trading days a year, and then figuring out annual returns and such by dividing, let's say, 834 tradings days by 252, so ...

Is there a way to schedule a email to send out at a certain time?
I tried delay the mail, but the sent hour is not right. For example, the email should go out at 8am, but the sent time is 5:30pm which should be 8am also. Please help! "Grace" <Grace@discussions.microsoft.com> wrote in message news:1395D87B-2982-4800-8522-165781125E6A@microsoft.com... >I tried delay the mail, but the sent hour is not right. > For example, the email should go out at 8am, but the sent time is 5:30pm > which should be 8am also. Please help! The Sent time and the delivery time are completely separate. The Sent time that people will see w...

conversion to time
Problem concerning the conversion of the following. Table input is as follows: Delay //Input time as a number with inputmask 01:23 00:00;0;_ 00:23 00:14 I need a conversion so that in the report the average time is displayed as 00:53 hours. I have the following: When is display the total time, the total time is displayed as 53.333. How do I tell Access that the input is a time input? Thanx Martijn martijnbomhof@hotmail.com ...

populate System Time in visual basic
How can I populate system time into a time field through VB. I am trying to populate the current time in time field in automated data collection window in manufacturing? ...

Conditional Sumproduct() Based on the Time of the Day
I was wondering how I can get a conditional Sumproduct to know what time of day it is and sum only the columns that meet the following criteria. 1) If it is before 11AM then all the "< Noon-1" Columns for each unit is added together in the "totals" column for each row of areas. 2) If it is after 11 but before 1PM then the "totals" column adds all the "Noon" Columns 3) If it is after 1PM then the "totals" column adds all the "> Noon+1" Columns There can be as many as 300 Units per month and every month has it...

email
The time recorded on all my incoming email is clearly and unequivocally incorrect, by several hours. I haven't yet checked to see if my outgoing mail is arriving at its assigned destination also with an incorrect time. My OS (Vista) date/clock is showing the correct values. Can anyone explain why this is happening and how I might resolve this issue? I thought I was undecisive but now I''m not too sure "Pompeygill" <Pompeygill@discussions.microsoft.com> wrote in message news:4795D99B-8C65-433A-94CA-2E9AAADA619D@microsoft.com... > The time recorded on all ...

time and curency
if i wanted to put 2 times in and get excel to work out the time take in hours between it and then times that by another cell which is th hour rate in � giving a out put in � how would i do it? i cant get excel to take the hours worked and convert it into mone earned. for example 4:55 hours and �5 hourly rate would be �24.58 but cant get excel to do this. please help thankyou Da Attachment filename: wages.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62592 -- Message posted from http://www.ExcelForum.com Hi! If the earlier ...

excel office home and student freezes all the time
I purchased office home and student 2007 several months ago. I have Vista on my computer. I had issues with Word, but was able to fix it. I have had no reason to use Exce, until now. When ever I open it, it constantly freezes up, with the little circle spinning. It says all the updates are on my computer. I have no idea how to fix this issue, and it is very frustrating, and time consuming trying to fix this issue. Thanks for you help. Steve ...

Linking Excel and Project for update-able timeline?
I've been assigned the task of creating an Excel workbook listing a whole bunch of project information. Someone had the "wonderful" idea of making a Gantt-type timeline. I've done that before in Excel without much trouble (once I found the instructions!). But this is going to be constantly updated. I wonder if it wouldn't be better - if indeed it's possible at all - to create the timeline chart in Project and link it back to the info in Excel. I've never used Project, and have no clue what this would involve. I just know Project does timelines better than Ex...

Averaging time values
Now that I have my input working, I want to be able to average the contents of a given range of cells. So, given: 00:00:10 00:00:30 00:01:25 00:00:16 00:02:37 00:00:08 00:00:02 I want to wind up with 00:00:44 Just using =AVERAGE(F4:F27) doesn't do the trick. One issue is probably that "empty" cells don't appear as 0 but 12:00:00 AM -- John Oliver, CCNA http://www.john-oliver.net/ Linux/UNIX/network consulting http://www.john-oliver.net/resume/ *** sendmail, Apache, ftp, DNS, spam filtering *** **** ...

Transfer time hh:mm to decimal and round
Anyone know how to transfer a time (hh:mm) into a rounded decimal? example: 3:08 to be 3.25?? -- Message posted from http://www.ExcelForum.com I'm not sure if this is what you're asking, but if I have my time i cell A1, my formula might look like this... =(HOUR(A1)*60+MINUTE(A1))/60 Format the cell as "number" showing as many decimals as you need. Th time conversions would look like this: 3:15 would yield 3.25 4:30 would yield 4.50 (with decimals set at 2) 13:12 would yield 13.20...etc. I'm assuming you want the decimals to show the minutes as fractions o an ho...

selecting gp company within crystal at run-time
I'm using GPv8 and Crystal 10(?). I have a report in Crystal that I want to be able to run against any of our multiple company databases in GP. For example, I want one of the parameters the user would enter to be "Select Company". How can I do this? Right now I have multiple versions of the report saved, with each pointing to a different SQL Server Database. Thanks, Rob Post your email address and I will send you a sample code which will let you process data from a selected company. I had to do it for myself to create a Consolidated Historical Trial Balance Report...

Wrong time in Outlook
Hi, I've got a really strange problem. My Mails in Outlook have all the wrong time - 9 hours back. This affects incoming and outgoing mails. It can't be a matter of the e-mail-provider because I've got accounts in gmx, web, yahoo, gmail and all have the same problem. Where can outlook get the wrong time ? The system time is correct of course. Thanks for your help. Ralf is the system time zone correct? whois shows your posting IP is in Germany... and windows defaults to pacific time zone so that would account for the difference. -- Diane Poremsky [MVP - Outlook] Author, Te...

Subtracting time values
I am trying to calculate the difference between two cells which ar formatted with Custom [h]:mm option. It has so far worked fine unti now, the higher of the two values is over 10000:00. I now get an erro stating that the higher value is not greater than the lower value. think it is because time values are represented by a date time syste which perhaps cannot work above values of 10000:00 -- fishphon ----------------------------------------------------------------------- fishphone's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2462 View this thread: http://www...

Sum of total Time
I need to Sum the total units of time that could be either entered in thousandths or milliseconds in a worksheet. Do I need a special add in? Any tips would be gratefully accepted. thanks Hi you may provide some example data of how you enter your values currently -- Regards Frank Kabel Frankfurt, Germany "Deltaecho" <Deltaecho@discussions.microsoft.com> schrieb im Newsbeitrag news:45968003-50B1-4651-A009-BAA7664BD8AC@microsoft.com... > I need to Sum the total units of time that could be either entered in > thousandths or milliseconds in a worksheet. > Do I ne...

How do I calculate charges based on elasped time(H:MM) & rate($)?
How do I calculate charges based on elasped time & rate? I want the time spent on a service to display in HH:MM Then I want to multiple this elasped time by a rate, say $25/hour. I can put this rate on the form and keep this cell outside the print area, or I can type "25" into the fomula, either will work for me. When I try to input a custom format, as described in help to be <h>:mm (where <> are square brackets), the data input shows in the input field as 1:30 AM, even after deleting the "AM" part, so it refuses to treat this as elasped time. Ple...

Chart frequency of date/time value...?
This is probably simple but frankly, Excel and math are not my strong points and never were so please, I need some help. I have just one column that contains date/time values down the the second, i.e.: 11/6/2004 12:00:10AM 11/6/2004 12:00:24AM 11/6/2004 12:00:27AM I need to group this data somehow into ten or fiteen minute intervals and bar-graph the number of rows tallied per interval. Any help greatly appreciated! If anyone cares, btw, the data is actually from a firewall log and I need to determine traffic trends through the day(s). Thanks, -B Joe - This is a histogram. There are so...

Time / Negative Time
Hi, If, for instance, in Excel you write 17:00 - 16:30, it will produc 0:30. However, 16:30 - 17:00 will produce #####, understandibly because you cannot have - ? o'clock. By formatting this to a number can produce -0.5. What if I still want to represent the decimal, but out of 60, so that can still get -0.30. I have tried messing about with fractions and still can't get it. Any ideas? Thanks, -- Message posted from http://www.ExcelForum.com On Thu, 13 May 2004 17:33:58 -0500, Kiaat <<Kiaat.167uek@excelforum-nospam.com>> wrote: >Hi, > >If, for insta...

Remove Date Timestamp in Excel?
Hi there, I was wondering if someone can help me out. I am using a csv file and want to calculate if things were done on time or not. Example: In the A column I would have "Date Expected" In the B column I would have "Date Completed" In the C column I have an IF formula such that if B<= A then "Meets" otherwise "Not Met" But I have a problem since I get the data from a csv file. The timestamp can give the impression that something does not meet. Example Date Expected: 09/18/2006 1:00am Date Completed: 09/18/2006 2:00am As per the formula it w...

Employee Time Tracking
I would like to create a database that will track employee times. Our company does not have any software or badges for this and our department is supposed to create something as sort of a pilot for employee time tracking. Someone suggested using Microsoft Access as the program for doing this. I know I can't be going about this the right way and I need help. So far, I have a Master Table [Master] set up that contains all employee times. Here are the fields that are in the table: [Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2 Out], [Break 2 In], [...

help: install prompt every time i start visio 2003
Hello all, every time I start Visio Pro 2003 SP1, I get an install prompt telling me that Visio could not find an additional module or one of it's components. It asks me if I want to repair....I say yes and everything seems to be ok. The problem is that this occurs every time I close and reopen Visio. Please help Thanks ...

Time Stamp-With Change
I seen many posts on in this forum on time stamps. My problem this tha in a1:a10 are going to be the entries. I need upon each entry into eac cell a time stamp in b1:b10, but I need this to be hard coded so that i I went back in a1and made a change it wouldn't change the time again i b1. Regards Anton -- Antony ----------------------------------------------------------------------- AntonyY's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1669 View this thread: http://www.excelforum.com/showthread.php?threadid=32046 Antony Try this in a worksheet module....