Help! Search & Replacing time formats

Hi

I have a whole spreadsheet full of fields similiar to the following

10:00AM 12:15PM 2:25PM  4:40PM  7:00PM  9:20PM 10:00PM
12:20PM 2:25PM  4:30PM  7:15PM 
10:00AM 12:30PM 2:45PM  9:15PM 
10:40AM 1:10PM  5:00PM  7:20PM  9:35PM 

etc.

I need to convert all times to 24 hour, and drop the AM & PM.

The later is easy, but how do I do a quick Search & Replace without
incorrectly converting 10:**AM to 22:** etc.

Any tips would be appreciated.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/7/2003 9:43:04 AM
excel 39879 articles. 2 followers. Follow

1 Replies
344 Views

Similar Articles

[PageSpeed] 32

glenster,

If they are really times and not just strings, then you can simply
format them for military time.

If they are strings and not times (the formatting won't change the
displayed value if they are strings), you will need to replace

PM

with

:00 PM

(Same for AM --> :00 AM)

Then enter a 1 into a blank cell, copy it, select all your time cells,
right-click, choose pastespecial... values, multiply.

Then format for military time, and you're done.

HTH,
Bernie


"glenster" <glenster.uxasz@excelforum-nospam.com> wrote in message
news:glenster.uxasz@excelforum-nospam.com...
> Hi
>
> I have a whole spreadsheet full of fields similiar to the following
>
> 10:00AM 12:15PM 2:25PM  4:40PM  7:00PM  9:20PM 10:00PM
> 12:20PM 2:25PM  4:30PM  7:15PM
> 10:00AM 12:30PM 2:45PM  9:15PM
> 10:40AM 1:10PM  5:00PM  7:20PM  9:35PM
>
> etc.
>
> I need to convert all times to 24 hour, and drop the AM & PM.
>
> The later is easy, but how do I do a quick Search & Replace without
> incorrectly converting 10:**AM to 22:** etc.
>
> Any tips would be appreciated.
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from
http://www.ExcelForum.com/
>


0
deitbe (120)
10/7/2003 12:41:50 PM
Reply:

Similar Artilces:

HELP!! #2
Does anyone know how to Synch AIM mail to outlook? ...

convert Date formate (windows default) to Arabic
Dear all, any one have an idea about how i can convert the Date from gorgian Date formate (windows default) to Arabic Um AL Qura format (Hijiri) ? -- MBS Developer Abdelrahman Nabil ...

Overrule/Replace
I have a row where I will be using tally marks. I only want one tally per row but it is based on a progression to track steps in the process. So if they have reached the next step they can just put a tally (an X) in the new cell and it will auto replace the previous tally. Thank you for any help. eg. Step 1 Step 2 Step 3 Person 1 X Person 2 X Person 3 X See my reply in .programming. And please don't multi-post... Bernie MS Excel MVP "40lbsofPoop...

Primary Axis(X) Formating Issue
I'm using a custom type chart (Lines on 2 Axes) and I'm using two different ranges for the Category (X) axis labels and the Second category (X) axis labels in order to display some additional information on the chart. When I say two different ranges I mean the rows are the same, but the columns are different. The problem I'm having is I want to use a custom format (dd-MMM-yy) to display the dates, but when I do so the dates have format MMM-yy-dd. The only way I can get the dates to display accurately is to use the Date format with the * (either *03/14/01 or *March 14, 20...

money 2007 vs. providian ? please help
money wont retrieve info from providian. if i look up providian in the ms money compatibility link as posted by others I get 'direct services' what gives any help? In microsoft.public.money, newby wrote: >money wont retrieve info from providian. > >if i look up providian in the ms money compatibility link as posted by >others I get 'direct services' I don't think so. It looks like "third party". > >what gives > >any help? ...

how to use the search dialog box ?
Hello Everybody, I have a question concerning the opening of dialogs box. I would like to open the Search Dialog Box. My stupid code is like this : Private Sub CommandButtonSearch_Click() 'ouvre la boite de dialogue Rechercher Application.Dialogs(xlDialogFormulaFind).Show End Sub With this code, the problem is that the Search Dialog box is indeed open but whatever the text to find in the worksheet, there is no result. If I stop the macro, open by hand the Search Dialog box and type the same text, it works... Can you help me to solve this problem ? Thanks a lot in advance. ...

Calculating transactions by time
Here's my problem, I have customer transaction data that shows deposits and withdrawals for a month. My data includes the customer ID, type of trans, date of trans, time of trans and amount. I am trying to come up with a way to count the number of times a customer makes a deposit then immediately makes a withdrawal. Obviously this will occur on the same date and within a minute or less of the deposit. I can pull a query showing the two relevent transactions together in a table but how do I count the number of occurrences. Any help is appreciated. Thanks! SELECT COUNT(...

Bar Graph Help
I'm trying to create a graph similar to the bar graph shown in the middle of the dashboard in the link below labeled as "YTD % of target", but don't know how to get the overlapping bars in excel. Can anyone advise? Thanks! http://www.bonavistasystems.com/Download2/Airline%20Dashboard2.png Here's one way to do it... Add the YTD % series to your bar graph, and then format the series so that it is on the secondary axis. XL will probably create a secondary X-axis, but that will cause a distortion of the data. We need them to be plotted on the same x-ax...

network time
if one of my domain controllers in a site has the windows time service stopped will the clients/server authenticating in that site still look to the pdc for time? if i run the w32tm /resync command on a machine when the service is running is syncs successfully. If the service is stopped i get: C:\ w32tm /resync Sending resync command to local computer... The computer did not resync because no time data was available. PDC is in another site in the domain. Is this normal? I cant seem to find any documentation on this exact question. figured as much. only one dc in the ...

very custom formats
I need help with a custom format If I enter the numbers 123 I want it to display 12-3 in the cell. (i.e simple custom format ##-# But if I also want to be able to enter 123A into the same cell and have it display 12-3A (where A could be any letter A-Z).. What would my custom format need to look like in order to do that Or is there a better approach? Not sure you can do what you want because the alphabetic character means the value is no longer numeric, hence a custom number format won't work Regards Trevor "mrdigithead" <anonymous@discussions.microsoft.com> wrote i...

Help Downloading OS to Target
Hi all. Kind of new to CE. I have managed to build a CEPC OS and have tested it on MS Virtual Machine. Problem I am having is trying to download it to the CEPC. From everything I can find, I need to have a 1.44 boot disk. Neither of my machines have a floppy drive. Ultimately, an ISO that can be burned to CD would be great! Any help would be greatly appreciated. Thank you. That depends on the media that your BIOS supports for booting -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/bruceeitman Eurotech Inc....

Split combined date time data #2
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See one answer in reply to your slightly later post. In article <EDD7065A-E5AA-40AB-BF38-50707E04109B@microsoft.com>, "Mark Ada" <MarkAda@discussions.microsoft.co...

Outlook 2003 event with no specific time that occurs on the 1st and 15th of every month?
What is the best way to make an event with no specific time that occurs on the 1st and 15th of every month? Make it an all day event? Recurring on the 1st and 15th - 2 separate events works better. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Kevin Zawicki asked: | What is the best way to make an event with no specific time that | occurs on the 1st and 15th of every month? ...

adding search folder
Under all mail folders I show I show two headings- "personal folders" and "fap..." which has all my e-mail. personal folders has a "search folders" folder which does not search my "fap..." folders. I would like to add a "search folders" so I can easily find flagged messages in my e-mail account. I would like to have the new search folder folders stored on my server as are all my other folders in the "fap.." folder set. How do I “search folders” to the e-mail folder list? AFAIK You can't add a Search Folder that searches...

Percent of time
Hi I would like to know how to get percent of time? i.e. 30% of 12:00 or 70 of 11:00 and still able to sum up / add-up the total results automatically? Thanks! If the source times are real times, just multiply it, and format the formula cell as custom: [h]:mm to enable rollover of results exceeding 24 hours. Eg if you have in A1: 12:00, you could have in B1: =870%*A1, with B1 formatted as custom: [h]:mm. B1 will return: 104:24 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jay" wrote: > Hi I would like to know how to get percent of time? i.e. 30% of...

Replace data by VALUE or COMMENTS not availabel in Excel 2003 ???
I upgraded to Office 2003 and noticed that the option to find and replace data by VALUE or COMMENTs is not available to me anymore. I can only look by FORMULAS. Is it something that I have left out when doing the Advanced Options Install or is it no longer available? Click Options to further define your search, and then do any of the following: In the Within box, select Sheet or Workbook to search a worksheet or an entire workbook. The steps used to be.... 1. In the Search box, click By Rows or By Columns. 2. In the Look in box, click Formulas, Values, or Comments. Now number 2 only sho...

Pivot Table Help #2
I have a sheet that I use to keep track daily of the value of inventory by day, and grouped by week. The value of the inventory is tracked by three different subgroups. (Below is an example). On my piviot table the values are grouped by type and by week. So what I get is a five day average of each Type by week, which is exactly what I want. My averages are as follows: M $796091.00 O $615069.00 R $3180669.00 =================== Grand Totals $1530609.00 The grand totals are where I need help. The grand total is an average of the avaerages. Is it possible to display a sum of the av...

Word 2007
I have created several tables in a Word document. I would like to know if there is a way to copy the table formatting from one table to the next while still mainiting the information that is currently in the table. Well, one way you could do this would be to:- - click in the Table - TableTools - Design contextual tab - Table Styles group - choose a Style in here and then apply the same Style to the other Tables that you have. If my comments have helped please hit Yes. Thanks. "Sammy" wrote: > I have created several tables in a Word d...

How to save custom cell formatting?
When I goto Format - Cells - Custom and type my own format. For e.g. #,###,##0.00000 The format mentioned above is available for current file only. How do I make it available to other files (and new ones) as well? ...

Really Need Some Help Here
RMS V 1.3.1006 We have a closed location. We do not have their database (this was our first closed location - 1 yr ago). This location has inventory that resides on it. Their computer has been retasked, and wiped clean for the new location. We have reports that pull that location's inventory. That is bad. I have worked around the RMS reports to add filter qualities, like Store Region <> Closed, etc. However, now we are also using a separate reporting tool that does not function like this. Does anyone (please help me!) know a query that can be run from the HQ side to compl...

I need help with Ex5.5 OWA
Can someone please tell me how to set it up. I'm new to this company and I'd like to set this up for them as an alternative to check emails. My Enviornment: Windows 2000 Active Directory Exchange 5.5 You help will be appreciated. "nadia" <nadia@discussions.microsoft.com> wrote in message news:CA4EED49-AEBD-4B43-8D69-87CD7AB1EF72@microsoft.com... > Can someone please tell me how to set it up. > I'm new to this company and I'd like to set this up for them as an > alternative to check emails. > > My Enviornment: Windows 2000 Active Directory...

Set begin/end times in a bar chart
I am trying to set up a employee shift schedule with a floating bar chart for approx. 30 employees. I can get the bar to start at the right time, but it does not end at the correct time. How can I accomplish this? I have to print out a daily schedule on a weekly basis so I would like to create a reusable chart for this function. I am using Excel and Powerpoint. Thanks for your help. Hi, Have a read of Jon's article on Gantt charts. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 He also has a collection of links on the subject. http://peltiertech.com/Excel/Cha...

why can't I access conditional formatting?
I cant seem to acces the conditional formatting thing in the formulas pull down. I would really like to use this feature, It just is always unavailible If your workbook is shared, CF is unavailable. See 'Features that are unavailable in shared workbooks' in help -- HTH Bob Phillips (remove nothere from email address if mailing direct) "dennis" <dennis@discussions.microsoft.com> wrote in message news:3B543FFC-BB33-4057-9AC8-B068DEEF6907@microsoft.com... > I cant seem to acces the conditional formatting thing in the formulas pull > down. > > > I w...

Help is "blank"
I had to reinstall windows and MS Office XP on my computer. Now the "Answer Wizard" and "Index" tabs are blank. The "Contents" tab still has the help tree. What did I do wrong? See http://support.microsoft.com/?kbid=319956 "The Answer Wizard and the Help Index tabs appear blank when you use Help in an Office program". Joseph wrote: > I had to reinstall windows and MS Office XP on my computer. > Now the "Answer Wizard" and "Index" tabs are blank. The > "Contents" tab still has the help tree. > > What did...

To Bernard or anyone who can help
Thanks for your reply Berbard. I can make a column or a bar chart without worrying about the order but here, I want to make the chart in order of score, in this case from highest to lowest but want to keep my spread sheet looking AB,B,C,D . . . . No, I don't have 4 entries. I have 32 entries. Hope this is sufficient. Thanks again for the prompt reply. Awaiting another reply Thanks Dharsh "Bernard Liengme" wrote: > Are you able to make a Bar or Column chart without worrying about the order? > Tell us the answer, and I will show you how to order the data. Can we ...