putting date and time together in 1 cell

Hi I need to convert New Zealand time to UK time, the time 
is displayed in two parts
A             B
date          time
dd/mm/yy      hh:mm:ss

How do i get cell A and cell B into one cell with the 
format dd/mm/yy hh:mm:ss and then I can just minus 13 
hours.

any ideas please.


0
anonymous (74722)
2/22/2005 2:52:41 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
359 Views

Similar Articles

[PageSpeed] 20

=SUM(A2,B2,-13/24)

and format the cell as dd/mm/yy hh:mm:ss.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi I need to convert New Zealand time to UK time, the 
time 
>is displayed in two parts
>A             B
>date          time
>dd/mm/yy      hh:mm:ss
>
>How do i get cell A and cell B into one cell with the 
>format dd/mm/yy hh:mm:ss and then I can just minus 13 
>hours.
>
>any ideas please.
>
>
>.
>
0
jasonjmorin (551)
2/22/2005 3:02:36 PM
try
=text(now(),"dd/mm/yy hh:mm:ss")

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Robbie" <anonymous@discussions.microsoft.com> wrote in message
news:106601c518ee$28bbb340$a401280a@phx.gbl...
> Hi I need to convert New Zealand time to UK time, the time
> is displayed in two parts
> A             B
> date          time
> dd/mm/yy      hh:mm:ss
>
> How do i get cell A and cell B into one cell with the
> format dd/mm/yy hh:mm:ss and then I can just minus 13
> hours.
>
> any ideas please.
>
>


0
Don
2/22/2005 3:03:29 PM
Reply:

Similar Artilces:

Pre-formatting Cells to look blank until data is entered
Using Microsoft Excel 2003 Is it possible to pre-set formatting on cells so that the formatting on those cells stay blank until data is entered, then the pre-set formatting is autmatically applied? For Example: Instead of having the entire sheet with Border Formatting, and having pages with blank data w/formatting, the pages will look blank until data is entered, then the formatting will be applied. Does that make sense? Troy Troy, For example, you could use an event: this will copy the format of the cell above after the entry has been made, for any single cell entry in columns A or B...

Date formatting problem.....
Hi All, I use the following code to create a string containing the current date. Dim sDate As String sDate = Format(Date$, "mmm d, yyyy") This code works fine here in the US, but a user in the UK is reporting that it's giving incorrect results. For example, on Nov. 5th, 2009, this code creates the string 'May 11, 2009' on the UK machine. Obviously, the code is confusing the month (11) and the day (5) and transposing them. I've played with this a bit, but haven't found a solution. Suggestions? Thanks, John Nobody wrote: > "John...

Date Format #2
I have copied and pasted a word table into excel which was a pain in itself. The date format in word was Thu 21st June 2007. But the table would not sort into date order so I had to remove the "Thu" part. I then had to remove the paragraph sign as it was throwing extra cells in excel when I pasted it. Eventually the date went into excel but now I cannot format the date. When I look at the format 21st June 2007 it has defaulted to General under "format cell". When I change the format to say custome date it does not make a difference, the only way it shacges is if I man...

One cell takes multiple inputs
I was wondering if there is a simple way to make one cell takes multiple inputs, essentially turning a cell into a placeholder. Example: Sheet1!A2=Sheet1!A3+5 Sheet1!A3 will take arbitrary value from Sheet2!A3 or Sheet3!A3, and return the answer to Sheet2!A4 and Sheet3!A4, respectively. If Sheet2!A3=2, then Sheet2!A4=Sheet1!A2=7 If Sheet3!A3=4, then Sheet3!A4=Sheet1!A2=9 Thank You. csw -- csw78 ------------------------------------------------------------------------ csw78's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23008 View this threa...

Immediately jump to next cell?
I have a chart with 5 rows and in each cell will be only one number. How to create that immediately after I insert a single number it jumps to next cell below without even pressing enter? Thanks in advance! You cannot do this. When you are entering data in a cell, Excel needs some type of signal that you have finished with that cell. ENTER or Tab or mouse-click or arrow out of the cell. Code will not work while in editing mode so VBA will not get you there either. Gord Dibben Excel MVP On Thu, 24 Nov 2005 21:54:51 +0100, ivankamastelic@yahoo.com wrote: >I have a chart with 5 rows an...

Cursor Keys does not change cell focus
I use passwords to protect worksheets (leaving cells unprotected in which data is to be entered) and so I do not forget the password, I write the password in the last row of the last column (IV65536) then I protect the sheet. When the sheet is protected I then cannot use the cusor keys or rather the up/down/left/right arrow keys do not move the cell focus to the next cell of the unprotected cells. I can mouse click on the cell but that is a pain and I should be able to use the cursor keys. If I then delete the data in the last column/row I am still stuck with the problem. Is this an undocum...

Determine date based on day of month (i.e. 2nd Tuesday of the month)
I've used Excell for years and I'm usually pretty good at finding answers to my questions. However, this one has eluded me. I figure the answer is simple and I'll have a "DOH!" moment when it's pointed out... I'm trying to determine the date of the 2nd Tuesday of every month in 2007. Ideally, I'd have two columns. First would have the month, the second would have the specific date of the 2nd Tuesday for that month. Can someone kindly point me in the right direction? I can't seem to figure this one out. http://www.cpearson.com/excel/DateTimeWS.htm#...

Smart List Export to Excel
When I export smart list data to an Excel Spreadsheet, I cannot use the month function on date fields. They look like dates, and have serial numbers like dates, but the function returns 01-04-1900 instead of the month. Is this an Excel issue or an issue with date fromatting from samrt lists? thanks -- Bob G Bob, What versions of GP and Excel? I just tried it on my GP 9.0 SP 2 and Excel 2007 - dumped a bunch of Account Transaction lines to Excel, and the MONTH function worked correctly...... -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Bob Gunderson" &l...

Subtracting times
Hi all, What formula can I use to subtract 2 times so that I get the number of minutes the one time is either ahead or behind the other. For example, if the deadline time is 08:00 and the actual time is 8:30, I want a result of -30 or even better, -00:30. It works fine the other way, where the actual time is before the deadline time. Many thanks. Rob Rob, If it is okay to have the result as a text string, that is you don't want to use it for math, then you can use =IF(A1<B1,"-","")&TEXT(ABS(A1-B1),"hh:mm") -- HTH RP (remove nothere from the...

Daylight Saving Time & RMS
Since the daylight savings time is this weekend, should I expect to have the PC's that have RMS running on them adjust the Win XP Pro clock manually. The reason, I am asking is that these PC's are on an internal IP and are not connected to the internet and do not get updates or synchronization to the clock server. On Mar 9, 10:55 am, Tom <T...@discussions.microsoft.com> wrote: > Since the daylight savings time is this weekend, should I expect to have the > PC's that have RMS running on them adjust the Win XP Pro clock manually. The > reason, I am asking is that ...

Changing Time format in Date column
Hi, When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm I would like it be shown as 05/03/2010 16:35. Any help from anyone would be greatfully appreciated. Thanks, Jon. "Jon" <Jon@discussions.microsoft.com> wrote in message news:54D681D7-2367-434B-AC7A-29AD80A4A828@microsoft.com... > When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm...

How to convert data/time to string in query?
How to convert date/time to string in query? So I can handle it with using "Left" or "Right" function. Thanks for any help in advance! You can do that with the Format function. However, if your purpose is to extract a part of the date, you can do that in a more locale-independent way using the various functions designed for that purpose, such as Year(), Month(), Day(), etc. Here's an example that on my system (where short date format includes four-digit years) returns the same value in both columns (albeit one is a string and the other is an integer). The first...

excel 2007 filter 3 text values at the same time from the same co.
I have a column that has these values AA, AX, AW, RJ, WD, CA, IE, NC Now I would like to filter AA,AX and IE only. How can I do it with just one step instead of filtering each one separately, copying and pasting onto another worksheet. Please, help Data>Autofilter then clear "Select All" and select AA, AX, IE Gord Dibben MS Excel MVP On Tue, 6 Apr 2010 12:25:01 -0700, Ivanka <Ivanka@discussions.microsoft.com> wrote: >I have a column that has these values AA, AX, AW, RJ, WD, CA, IE, NC >Now I would like to filter AA,AX and IE only. How can I do i...

Need to generate a date if cell is not bold...
Hello, I need to generate a date in cell V2 that is 5 days prior to the date in cell S2 IF the date in S2 IS NOT bold. (I have conditional formats in S2 that will bold its date in certain circumstances). Hope this isnt too confusing. Any help is appreciated. Regards Max This will require a macro. Formulas cannot check the format of a cell. To do it with a formula, simply replicate the same conditions as your conditional formatting. So V2 would look like: =if(yourconditions,v2-5,whatever you want when V2 is bold) Regards, Fred "Max" <Max@discussions...

Auto update a date field when a checkbox is ticked
Hi All Does anyone know how I can automatically populate a data/time field when a check box is ticked? This needs to be at record level. thanks Submitted via EggHeadCafe - Software Developer Portal of Choice Get Identity Values of Multi-row insert by using OUTPUT Clause http://www.eggheadcafe.com/tutorials/aspnet/03c8104d-7deb-425e-beb5-dd4f996da99f/get-identity-values-of-mu.aspx If you mean when the check box receives a check (rather than when it is cleared), in the check box After Update event: If Me.CheckBoxName = True Then Me.SomeField = Now() End If If you want th...

CDialog::DoModal() == -1
Hi, I write a SDI Applikation wich use 2 nonmodal Dialogs as Controls created with ::Create now I will use a modal Dialog in a Function called by a menue message CObjectDialog od; int ret = od.DoModal(); ret is always -1 I tryed to create the same Dialog ressource nonmodal with create, but its also not possible, ::Create returns 0 Is it possible to get a Information why DoModal does not work ? GetLastError ? Does anyone have a idea whats the Problem. Thx Peter DoModal nomally returns -1, whenever an error occurs and dialog cannot be created. I had a similar problem, when I tried u...

Writing Reports with local time reflected
Hi! I've been writing reports for CRM3 and all of my time stamps are being reported in GMT (as they should be). Does anyone have any advice for generating reports with these times reflected in the users local time zone? I'd like to write reports for users in different time zones, but have the times show up (for example activities) as they would have occured in local time. Thanks! Hi Sam, I'm no expert, but I would assume the reports are being generated on the SQL reporting server, which is where the time is calculated. I would imagine you could prompt the user for whic...

Internet Address for Bank Gets Overwritten Every Time Money loads
Every time I start Money 2005 Deluxe, it updates the Internet address for one of my credit card institutions. It must be pulling the address from some lookup table and overwriting the correct address that I have provided (by going into "Update advanced Web settings" from the Contact information screen and entering the correct address in all four fields). The account is United Mileage Plus Credit Card which used to be administered through Bank One, but recently changed hands to Chase. The correct address is: https://online.firstusa.com/ The incorrect address that Money keeps pr...

Medical CDs
Medical CDs - [Part 1], [Part 2], [Part 3 = MEDLINE 1986-1998] CDs, [Part 4 = Dragon Naturally Speaking CDs, and IBM Via Voice CDs, including Medical Solutions], [Part 5 = Math Solving, and Statistics Porgrams], [Part 6 = Various - Medical Cliparts, Medical Photodiscs, Medical Imaging Suites] buy, risk-free purchase, working, tested, fully functional, very cheap discounted price, low cost, quality OEM software, 2005/09/03 Vilppu Studio Pelvis Anatomy DVDrip 1CD 2005/08/20 Vilppu Studio Lower Leg Anatomy DVDrip 1CD 2005/08/20 Vilppu Studio Proportion DVDrip 1CD 2005/08/20 Vilppu Studio Feet...

embed a picture into a cell?
howdy, i was wondering if it was at all possable to embed a pic into a cell instead of it floating about! the actually problem is, i want a cell to show a pic of a fil certificate (eg 15, 18) instead of just the numbers. The cell will b linked to another cell and using if equations to the basic effect of if this cell contains "U", show "u.gif", if not, does it contain "12a if so, show "12a.gif" and so on. so is there any way of embeding a pic whilst being able to use tht in if equation? thanks for your help Alvi -- Message posted from http://www.ExcelF...

Lookup row, dependant on cell value?
I have a sheet, as per the example. I need to insert into column A the quantities, depending on the value a specfic cell. I.e if week 2 is entered, the column A should read, 12,5,2,3,3,3 and if Week 1 is entered 5,3,2,3,2,2 etc I have looked at using HLOOKUP, however this does not replicate the reference cell corrected unless each line is configured serperately. What is the easiest way to acheive this. At the moment I need to select the column manually, and replicate down the sheet, rather than just enter the required week number into a cell, and let excel adjust automatically. ...

how do i print a list of names and dates in excel into a two-colu.
i have a list of names and dates comprising three columes in excel. How do i print this list in an array two colums wide on 81/2x11 paper. i.e.: NAME....DATE....DATE NAME...DATE....DATE there is enough data to fill four pages. data needs to be sorted so that new names can be added alphabetically. in page setup on the sheet tab, try selecting "over, then down" as the page order. "Captainbob" wrote: > i have a list of names and dates comprising three columes in excel. How do i > print this list in an array two colums wide on 81/2x11 paper. i.e.: > ...

Entering Multiple Items in a Single Cell
In gmail, when I click 'Compose Mail' the cursor is placed in the 'To:' textbox. The behavior that takes place in this textbox is what i'd like to achieve in Excel. When I begin to type a contact name, a smart dropdown list presents all the contacts that begin with the text that i have typed. This dropdown refreshes with every keystroke I make. By default, the first item in the dropdown is always selected. If the first item is the right one, I can click the 'Tab' key and the contact's name is completed in the textbox. I could have also opted to arrow...

Register OCX more than one time
Hi I'm using VC 7. Can someone tell me what is the simplest way to register my OCX two times on the same computer (the two OCX's are not binary compatible, but should have the same name). The problem is that sometimes it happens that my OCX will installed from different providers on the same computer and the OCX from the providers are not always binary compatible. Werner "Werner" <activeengine@web.de> wrote in message news:c92dffd3.0311070702.2d170039@posting.google.com... > Hi > > I'm using VC 7. > > Can someone tell me what is the simplest way t...

Adding a second or separate line within a cell
How do I add a second line or paragraph within a cell? use Alt enter to start a new line -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "thefish" <thefish@discussions.microsoft.com> wrote in message news:DC3007CD-A8D7-4AF2-8FE8-8A605CD75F12@microsoft.com... > How do I add a second line or paragraph within a cell? ...