How do I convert US date with 12hr format to European date 24hr

I am using Excel. I have a mix of 24hr and 12hr AM/PM format pulling from a 
database. I want to reformat all dates with time to the 24hr clock so that I 
can then have 1 format to manipulate
0
Utf
11/15/2009 8:41:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1491 Views

Similar Articles

[PageSpeed] 11

If the input data are formatted so that Excel recognises them as times, then 
it doesn't matter whether they are 12 hour or 24 hour format.  If you want 
to change them all to one format, use Format/ Cells/ Number/ Time, and 
choose to suit your preference.
You may have problems if your data are treated as text because they have 
spare spaces or non-breaking spaces.  In that case you'd have to change them 
to real times before formatting them.
--
David Biddulph

"Enda K" <Enda K@discussions.microsoft.com> wrote in message 
news:69F35F90-4839-426B-BD12-E727FFBAB1C8@microsoft.com...
>I am using Excel. I have a mix of 24hr and 12hr AM/PM format pulling from a
> database. I want to reformat all dates with time to the 24hr clock so that 
> I
> can then have 1 format to manipulate 


0
David
11/15/2009 9:59:07 AM
Reply:

Similar Artilces:

SQL statment to convert integer to string
Dear all, I have a select statement "select year from table1" , where year is an integer type. How can I convert the year to string in sql. I have try "select cstr(year) as year-string from table 1". But it is failed. Please help. On 21 feb, 09:48, hon123456 <peterhon...@yahoo.com.hk> wrote: > Dear all, > > =A0 =A0 =A0 =A0I have a select statement "select year from table1" , wher= e > year is an integer type. How can I > convert the year to string in sql. I have try "select cstr(year) as > year-string from table ...

Can't get dates to format corrrectly.
Hi, I ran a MS-Access query to pull in some data from our database and one of the date fields has the data formatted as either MM/DD/YYYY or YYYY-MM-DD. I want to change the data on the Excel sheet so that all rows display this date as MM/DD/YYYY. I tried to obvious of selecting the entire column and setting the format to just that date format but still those dates remain formatted as YYYY-MM-DD. When the data came into Excel it was set to a general format for that particular column. Any ideas on how I can accomplish this? You might try this: go to any blank cell off to the side an...

Dating using workdays
Is there a way to autofill dates into a spreadsheet and only use workdays and not Saturdays and Sundays? no Depending on version yes, put the first date in a cell, right click the cell and copy it down, when you release the mouse button you can select weekdays.. -- Regards, Peo Sjoblom "Bill" <anonymous@discussions.microsoft.com> wrote in message news:012a01c3d543$6c197a40$a301280a@phx.gbl... > no You could try this. Put a non weekend-date in A1 Put this formula in A2 =IF(WEEKDAY(A1)=6,A1+3,A1+1) Copy it down -- HTH Bob Phillips ... looking out across Poo...

Excel Bi-weekly time sheet change dates to Thursday- Wednesday?
I downloaded a Bi-weekly timesheet for Excel. The timesheet is perfect except for the date part. The time sheet comes with a Monday to Friday bi-weekly time set-up and I need to change it to a Thursday to Wednesday bi-weekly time set-up. At present I had to individually change the days starting with the Thursday and when I change the start date and ending dates the only days that automatically change are the first Thursday to Sunday settings (4 days). How do I set it up so that all days change when I put the start date and end date at the top of the sheet? Hi Starting with the cel...

Time Format #6
I'm having a problem adding together journey times. I have a start time (say 8:34:00) and an end time (say 10:23:00) and from this I calculate a journey time (1:49:00). I then add this journey time (using the sum function) across a number of different journeys giving me a total. This total is in some case in excess of 24 hours and Excel seems to revert to 0 at 24 hours and start the sum again. So for example I can see when I highlight the range that the total is 27:49:00 but the sum function returns 03:49:00. Any ideas? Cheers Lee Use a custom format: [hh]:mm:ss The []...

Input a date using a Form Button.
Hi, Please help as I'm totally lost with this one! I'm doing a little project for work and I have come across a littl problem with a date input that I have set up using a Form Button. No this button once pressed enters the date just as I want, into th correct Cell (the buttons called "Todays Date") Now I have recorded macro so that the button executes the =TODAY() comand - which is fin untill the date changes!! As you can emagine I need the date to stay a it was when first pressed as its a database I'm trying to compile. Does this make any sense to you all?? I will...

Generate Payment Due Date to exclude Weekends and Public Holiday
I have a table with Column A = Invoice Date Column B = Days of Credit Granted Column C = Payment Due Date Column E = List of Public Holiday with the first row as header and subsesquent rows as the actual data what i want to do is to auto generate the payment due date of each invoices so that it won't falls on weekends and public holiday. for example, if [Invoice Date] + [Days of Credit Granted] falls on a saturday, then the due date will be push forward 2 days to monday. i came out with a formula which works pretty fine given that there's no consecutive non-working days ...

In Publisher 2003, convert endnotes to footnotes
How do you convert endnotes to footnoests in Publisher 2003 How do you automatically place footnoes in Publishr 2003 In WORD its easy, but can't determine how to do it in publisher. If impossible in Publisher 2003 Is there a Utility that accomplishes this?? jerome <jerome@discussions.microsoft.com> was very recently heard to utter: > How do you convert endnotes to footnoests in Publisher 2003 Footnotes and endnotes are both things which don't exist as separate functions in Publisher. > How do you automatically place footnoes in Publishr 2003 You cannot. > If imp...

Date Function
I have two different users, who have the same machines and the same version of office. However when one of the users types in a date; i.e - 10/02/2005, and then goes to format the cell, they can click on a date type that will look like; "*Thursday 10 February 2005". However on the second users machine this function is not present, all they have the option of is; "*10 February 2005". I dont know why it is missing of the day?? Can someone please help me? -- andyb7901 ------------------------------------------------------------------------ andyb7901's Profile: http:/...

macro to find date format in a cell and delete that entire row
macro to find date format in a cell and delete that entire row i have dates in a column and date keeps changing, i want that if macro find date in a column , it deletes that entire row help me thank u so much everyone for ue hel -- Message posted from http://www.ExcelForum.com try Sub deletedate() For Each c In Selection If IsDate(c) Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "vikram >" <<vikram.15ix9t@excelforum-nospam.com> wrote in message news:vikram.15ix9t@excelforum-nospam.com... > macro to find date format in a ...

flip a sheet format
Hi, I am looking for a solution where I could change the table in a different format. Any office product solution, Excel or Access or any other tool could be used where the process can be automated (macro, SQL query, etc) I have this table: T1 App1, App2, App3 T2 App4, App5 T3 App6, App7, App8 I want to create this table of the previous table: App1 T1 App2 T1 App3 T1 App4 T2 App5 T2 App6 T3 App7 T3 App8 T3 Thank you for any help Hi The following code should do what you want. Sub ChangeLayout() Dim i As Long, j As Long, k As Long, lr As Long, lc As Long Dim wss As...

How can i change cell colour depending on month of date in cell?
I have a column with a range of dates corresponding to when an item was tested and would like to automatically change the colour depending o which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep blue and Oct-Dec= yellow. No date = no colour. Anyone have an suggestions? Do I need to run a macro or is there a simpler way? Thanks And -- andy7 ----------------------------------------------------------------------- andy75's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3017 View this thread: http://www.excelforum.com/showthread.php?threadid=49860 ...

COUNTIF function with date range
Is there anyway to use the COUNTIF function with a date range. I'm trying to count each date within a particular month. For example, =COUNTIF(G5:G100, "12/**/03" And this example with the wildcards is currently not working. Thanks. Amy Hi, Try, =SUMPRODUCT(--(MONTH(G5:G100)=12)) Hope this helps! In article <fd8501c43e96$22a9daf0$a401280a@phx.gbl>, "Amy" <anonymous@discussions.microsoft.com> wrote: > Is there anyway to use the COUNTIF function with a date > range. I'm trying to count each date within a particular > month. For ...

Converting varying length Vertically-aligned records to Horizontal Rows of list (tabular)
I have a Excel Spreadsheet with Records of books. The fields and data value is entered as the First and Second Column (ColA, ColB). The records are separated by 2 empty rows. Some fields are missing for some records: Some record may miss the 'author' whereas some records may miss some other fields, say ,'subject' AND 'totalpage'. The Objective is to tranform the record horizontally with Each ffield in one column with missing field blanked so that all the field are aligned in a column e.g. colA for 'Title', colB for 'Author' How can we convert varying ...

Inbox Date Stamp Is Wrong
In Outlook 2003, the date on received emails is already showing the year 2004. My system date is set correctly. I have sent and had others send me emails through both of my accounts and the date still comes through as 2004. Should I create a new profile? Is there a place to modify this somewhere in Outlook or in the registry? Thanks for your ideas. Dave hmmm. if your clock is right and the senders clock is right then .. is it an ISP problem ? is it the 'sent' or 'received' time which is wrong ? are they the same ? are they logical ? N "Dave T." <anony...

visio current date and time fields
How can i search a shape's text for occurrences of current date and time fields. Is there a way I can just search the text for a time field and a date field of any format? Thanks in advance. Hello Biggaford, The text of the shape will only return a mix of the inserted field and so you would be able to check for the the 'current date/time' as opposed to just an abitary date using Edit / Find. If you know the format that was used for the inserted fields you might be able to search using a pattern such as: ^?^?/^?^?/^?^?^?^? ....which will return a match for a date. Other...

Show only Journal entries in Delete bin, sort by deletion date
I posted a number of times about the problems I encountered with Ctrl- D (delete a Journal item) being right beside Ctrl-S (saving a journal item). On occasions where I actually notice an accidental deletion, one thing that could help is to be able to view only journal items in the Delete bin. Is there a way to do this? Another thing that might help is to be able to sort by deletion date. Is there a way to do this? Sometimes, I accidentally delete a journal entry from way back, and that basically means its buried in the Delete bin (which is normally sorted by date). Even if the d...

Email
I use Word as my email format. How do I display the horizontal ruler on a new email message I'm trying to send? ...

Multiple-Test Conditional Formatting
Hi, everyone! I have a spreadsheet in which one column (B) contains an item "type", e.g. "C" or "H". In another column (G), I put a "required" sub-item. I am trying to conditionally format the cell in column G to do the following: If Col B = "C" and Col G is blank, make background of Col G red; Otherwise, make background of Col G normal. Using normal CF, I can make G's background red if Col B = "C" (formula is), or I can make it red if Col G is blank (cell value is), but can't seem to find the trick of combinin...

formatting lost from word v.X to 2004
I have office 2004 installed on two computers in my home - a powerbook and a powermac. I was testing office 2004 on my powerbook and when I felt that everything was okay, I installed it on my powermac. Problem is, it didn't behave the same way on my desktop machine. Opening v.X docs in word 2004 on the powerbook was fine - all the formatting seemed to be retained. However, opening word v.X docs in 2004 on my desktop machine yielded quite different results. I saw two main problems: 1. all my bullet points were turned into infinity signs 2. somehow the formatting changed slightly enoug...

Excel 2007 How To Add New Number Formats to Styles
The Styles Functional Group in the Home Tab contains the following Style choices Custom Good Bad Neutral Data Model Titles and Headings Number Format When I created a new style it went into the Custom category but I'd like to move it to the Number Format choice. How can I add a new Style to a specific section of the Style functional group? Thanks for reading and for any help you provide. Regards (8^>)-]=[ ...

Printing Dates on One Sheet
I have one template that i print for our production floor. Each sheet has seven dates printed; Monday thru Sunday. I need to print this one template 52 times for each week of the year. Is there a formula that i could enter one day (i.e. the first date for the first monday) and automtically print the rest of the weeks. in essence, press the print button one time, but the same template will print 52 times with each week on the sheet? So you have 7 rows on each sheet and 52 sheets? If I were you, I'd collate everything on one summary sheet and filter your data there. Check...

Prevent format change caused by pasting. (office '97)
Every time I paste something that was from an website (even if only one word) the column extends as far right as it allows. I found out that I can change the format (under paste special) back to text to fix this. Is there a way to prevent excel for changing format? I want it to default as text format and not change format unless I change it myself. Also is there a way to keep the text at one font and one text size, so that if I paste something that had a different size or font it would change to the size and font that I have set for my document (I want to know how to do this in Word as well.)...

Transaction versus Post Dates
I'm a long time customer of Microsoft Money and I have recently ran into a problem that I believe would be solved with offering the ability to track transaction and account posting dates separately. Right now with Microsoft Money (I'm using version 2005), one must choose if he/she wishes to use the date as the time of the actual transaction or the date it posted to the account. Naturally using the posting date would be advantageous when sync'ing with your bank and performing balances. However, this gets me into a problem with taxes. I track sales tax of EVERY transaction...

conditional formatting for more than 3 conditions
Hi, Is there any solution for the following : I have six type of conditions, if any condition is met, cell colour should be yellow. For example, if cell value is any one of the following : DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 All cells containing above should show yellow colour in the file. Please help. Thanks, Jai formula is =or(c1="data1",c1="data2",etc) or =or(c1={"data1","data2"}) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Jai" <Jai@discussions.microsoft.com> wrote i...