Date and Time formular

I'm trying to take one date and time away from another but having a problem 
fathomining it out

eg

01.04.2008 10:55:27 - 01.04.2008 9:37:13

The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1

A formula for this would be great if anyone has one to hand

Thanks



0
Student.2 (9)
5/15/2008 2:21:06 PM
excel 39879 articles. 2 followers. Follow

3 Replies
414 Views

Similar Articles

[PageSpeed] 13

Just subtract

=End_Time-Start_Time


-- 


Regards,


Peo Sjoblom



"SS" <Student.2@uk.bosch.com> wrote in message 
news:g0hgsk$jgc$1@news4.fe.internet.bosch.com...
> I'm trying to take one date and time away from another but having a 
> problem fathomining it out
>
> eg
>
> 01.04.2008 10:55:27 - 01.04.2008 9:37:13
>
> The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1
>
> A formula for this would be great if anyone has one to hand
>
> Thanks
>
>
> 


0
terre081 (3244)
5/15/2008 2:37:13 PM
Well should they be formatted to anything particular I've tried that and 
unfortunately it didn't work.  Also it's not just A1 and B1 it goes down 
numerous columns


"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:OLfErkptIHA.4912@TK2MSFTNGP03.phx.gbl...
> Just subtract
>
> =End_Time-Start_Time
>
>
> -- 
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>
> "SS" <Student.2@uk.bosch.com> wrote in message 
> news:g0hgsk$jgc$1@news4.fe.internet.bosch.com...
>> I'm trying to take one date and time away from another but having a 
>> problem fathomining it out
>>
>> eg
>>
>> 01.04.2008 10:55:27 - 01.04.2008 9:37:13
>>
>> The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1
>>
>> A formula for this would be great if anyone has one to hand
>>
>> Thanks
>>
>>
>>
>
> 


0
Student.2 (9)
5/15/2008 3:04:18 PM
If you want the result in hours and minutes
and if it is more than 24 hours difference
use custom format [hh]:mm



-- 


Regards,


Peo Sjoblom


"SS" <Student.2@uk.bosch.com> wrote in message 
news:g0hjdk$m8f$1@news4.fe.internet.bosch.com...
> Well should they be formatted to anything particular I've tried that and 
> unfortunately it didn't work.  Also it's not just A1 and B1 it goes down 
> numerous columns
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message 
> news:OLfErkptIHA.4912@TK2MSFTNGP03.phx.gbl...
>> Just subtract
>>
>> =End_Time-Start_Time
>>
>>
>> -- 
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>>
>> "SS" <Student.2@uk.bosch.com> wrote in message 
>> news:g0hgsk$jgc$1@news4.fe.internet.bosch.com...
>>> I'm trying to take one date and time away from another but having a 
>>> problem fathomining it out
>>>
>>> eg
>>>
>>> 01.04.2008 10:55:27 - 01.04.2008 9:37:13
>>>
>>> The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1
>>>
>>> A formula for this would be great if anyone has one to hand
>>>
>>> Thanks
>>>
>>>
>>>
>>
>>
>
> 


0
terre081 (3244)
5/15/2008 5:00:27 PM
Reply:

Similar Artilces:

Excel Re-Calc causes network traffic and very slow response times.
Platform: Excel Ver 2003 SP2 Windows XP SP2 Microsoft SBS Server 2003 Workstations: P4 2Ghz, 512 Mb Ram Problem: I have an excel file with hundreds of vlookup and indirect formulas. It has no links to any other spreadsheets. The file resides on a shared network drive on our server. When users do a recalc or change a number in the file causing a recalc it takes about 7 seconds... however after a bit of experimentation I find that if we open the file and then disconnect the workstations network cable the recalc takes less than one second ! Tried saving the file to the local drive of a workstati...

Automating Time Scale X-axis
I would appreciate some suggestions on how to automate a chart's x-axis values. For instance, I would like to be able to have my chart update to the last 30 days of data so that each time the file is opened, the data is updated in the chart to show the last 30 days from the current date. Can this be done? Tushar Mehta has a tutorial for dynamic charts: http://www.tushar-mehta.com/ David Lawrence wrote: > I would appreciate some suggestions on how to automate a > chart's x-axis values. For instance, I would like to be > able to have my chart update to the last 30 d...

Sorting dates #2
I have date column set as: d/m/yyyy when I do a sort its sorting by day, I want it sorted chronologically, how can this be done? eg heres what my sort ended up as: 12/8/1819, 13/2/1835,17/12/1813,19/5/1833 Thanks Althea --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 Hi Althea if you have only dates in this column just sort them. This sort meachanism is Excel's default. -- Regards Frank Kabel Frankfurt, Germany AB wrote: > I have date column set as: d/m/yyyy &...

Help required on working with date
Hi I have dates in a column in dd-mmm-yy format, like 29-Mar-04 15-Apr-04 13-May-04 I want to put in the column adjecent to this date column the date starting with 1st day of each month like - 01-Mar-04 01-Apr-04 01-May-04 How can this be done by use of formula? I tried but do not get any clue to go futher. -Shanks Hi try =DATE(YEAR(A1),MONTH(A1),1) -- Regards Frank Kabel Frankfurt, Germany Shanks wrote: > Hi > > I have dates in a column in dd-mmm-yy format, like > > 29-Mar-04 > 15-Apr-04 > 13-May-04 > > I want to put in the column adjecent to this da...

USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...

Exchange 2003 System Manager Server Modified Date Meaning
Hi, I am looking for some info on the Exchange 2003 system manager server property page. It shows in the right hand colum all the servers and service packs and the furthest right says "modified date" Could some one please eleborate on what this actually means? Thanks On Tue, 31 Jan 2006 06:20:28 -0800, Darrel <Darrel@discussions.microsoft.com> wrote: >Hi, > >I am looking for some info on the Exchange 2003 system manager server >property page. It shows in the right hand colum all the servers and service >packs and the furthest right says "modified d...

Converting Text Field to Time
Is this possible? Me Probably, but what does the text field look like. -- HTH RP (remove nothere from the email address if mailing direct) "A P" <ap@textguru.ph> wrote in message news:uRvCdTHKFHA.3916@TK2MSFTNGP14.phx.gbl... > Is this possible? > > Me > > you might like to check out the TIME function, if you'ld like more assistance please post back with a sample or two of your "text" typed into your reply. Cheers JulieD "A P" <ap@textguru.ph> wrote in message news:uRvCdTHKFHA.3916@TK2MSFTNGP14.phx.gbl... > Is thi...

Date Expiration and highlight
Here is the problem folks If Today's date (in cell o2 or Today()) is older than the value date (10/31/07) in L4 cell P4 shlould display "Still valid" and cells between A4 thru P4 should display green If Today's date (in cell o2 or Today()) is newer than the value date (10/31/07) in L4 cell P4 shlould display "Quote Expired" and cells between A4 thru P4 should display red. I will donate 20$ to children with leukemia non profit organization. Regards On Oct 31, 6:50 pm, turkyildiz <turkyil...@gmail.com> wrote: > Here is the problem folks > > If...

count between dates help (NETWORKDAYS)
Hello big brains I am trying to find the days and hours between 2 dates 2 columns of dates formated in M/D/YYYY hh:mm 4/23/2004 15:53 and 4/27/2004 10:43 I don't want to count weekends so I used NETWORKDAYS which works to a degree.. giving me 3 (which is days) but it's ignoring the time. I need it to be specific and have it show days and hours difference. I am suprised that it is ignoring it. Unfortunately I am kind of stuck with the dat being in this format. Any help would be appreciated. thanks Maybe this can give you some ideas http://www.cpearson.com/excel/DateTimeWS.ht...

GETPIVOTDATA and Date-type field (Excel 2007)
Data Sample *************** Trip 0346210980 Statement Date 3/24/2010 Description STARBUCKS Date 03/08/2010 LocCurrAmt 3.47 Formula: =GETPIVOTDATA("LocCurrAmt",A6,"Statement Date",DATE(2010,3,24)) Problem: I keep getting a #REF error. When I evaluate the error it's always with the DATE function. Steps I've taken: 1. Confirmed that my date is a number and not text. 2. Tried using the date serial number (40261) instead of the DATE function. 3. Confirmed the GETPIVOTDATA works...

Copying and pasting a range x times based on user-defined value of x
I posted this in the members section, but haven't received a solution to my problem yet. Essentially I have a problem where I have a row with some user inputs and formulas that calculate based on those user inputs. There is another user input field (on another sheet) where they enter in a number ("x"). I'm trying to create a macro that will paste that row with formulas "x" times based on the user-defined value of the "x" field. In other words, if the user types in 40 in the user field, the macro pastes the row with formulas 39 times (sinc...

Run-Time Check Failure #2
Hi I am getting this error Run-Time Check Failure #2 - Stack around the variable 'tokenPrev' was corrupted. I am also paste the my code can anyone tell me how can I fix this problem ? //--------------------------------------------------------------------------------------------- BOOL CnewAppDlg::OnInitDialog() { HANDLE hToken = NULL; DWORD hTokenLength = 0; TOKEN_PRIVILEGES tokenPrev = {0}; if(OpenProcessToken(GetCurrentProcess(),TOKEN_QUERY,&hToken)) { if(!GetTokenInformation(hToken,TokenPrivileges,NULL,0,&hTokenLength)) {...

time calculation 08-14-07
Any Help Please. . . I am new in access, and im doing some calculations from my query design. I need to calculate two fields in a query. It is like cascading calculation that the new field is the result and that result will be added to 2nd and to 3rd row and so on. The calculation is on hh:mm in 24 hr format. please see example: OnChk OffChk On/OffDifference Total Hour 8:59 13:45 4:46 1110:57 => This is carry over figure 8:50 12:57 4:07 1115:04 7:48 9:49 2:01 1117:05 1. I got the On/OffDifference by us...

Todays date in a cell
Hi I have a spreadsheet that I use to record data. I only fill it in about once or twice a week. What I want to do is when I open the spreadsheet I want to have todays date in the cell below the last one i.e dates in column A However I then want the dates to stay as those dates i.e if I open the spreadsheet on 07/07/03 with that date in cell A12 when I open the spreadsheet on 09/07/03 I still want 07/07/03 in cell A12 and 09/07/03 in A13 etc. What do I put in cell A2 and fill down the column? Thanks Sophie Try this but put the sheet you want and the cell you want. If Date does not work ...

Excel 2003- Charts- date and time format on x-axis of XY chart
Hello, I am trying to plot an XY graph with x-axis data in date and time format but Excel keeps counting the x-axis data as "data labels". It is in the format mm/dd/yy HH:mm ex) 10/28/06 17:00 Instead, Excel appears to be turning my XY plot into a line plot (for example, the first date is assigned an x-value of 1, the second value assigned an x-value of 2 and so on so that each date/time x-value is evenly spaced). I would like to find a way to plot this data so that it is spaced relative to date and time. Are your data values actually date and time, or are they text? Does ...

Calendar date function
I would like to insert a calendar icon into a cell so that when I click on it, it will bring up a calendar from which I can then select the date that I want to appear in the cell. Hi John See http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl "John" <John@discussions.microsoft.com> wrote in message news:968D56F9-D1FA-4CB5-841E-94B51D717ED2@microsoft.com... >I would like to insert a calendar icon into a cell so that when I click on > it, it will bring up a calendar from which I can then select the date that > I > want to...

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

Date format on checks
How do you change the date format on checks? Money 2005 is using a day/month/year format and several payees have complained that 1/5/05 means 1 May 2005 and their banks are objecting. Have you tried messing with Region Settings for the operating system? "ROBERT L GUTHRIE" <rlguthrie@mindspring.com> wrote in message news:tcXHd.2206$cZ1.904@newsread2.news.atl.earthlink.net... > How do you change the date format on checks? Money 2005 is using a > day/month/year format and several payees have complained that 1/5/05 means 1 > May 2005 and their banks are objecting....

losing date format when referencing date cell on another sheet and
I need to reference a date cell in another sheet in my workbook. I select the cell that needs to display the referenced content. Then in the formula bar I type = . Then I move to the sheet containing the cell I want to reference. I select this cell and then click the green checkmark next to the formula bar. The desired date value now shows up in my display cell in the correct format. But I need to add text to this cell. Now my display date loses its format and becomes a plane number. Here is my reference =Sheet1!B15 Here is what I want to do = "(" & She...

Increase a date by 1 month
Hello, I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK =DATE(YEAR(I1),MONTH(I1)+1,DAY(I1)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message news:u98STsetJHA.5652@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a date in in one cell, eg 22/04/08 and I want the next cells to be > one mon...

Email Time Stamp Wrong, Calendar OK (Time Zone?)
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Last week I began having an interesting problem in Entourage where the time stamp on every email message (3 different accounts, 1 Exchange, 2 IMAP) is off by 6 hours. I have searched high and low and found various information on where to verify the proper time zone settings and have done so. <br><br>What makes the situation even more interesting is that it affects all three accounts, but when accessing each of those accounts with another email client (Web, iPhone, etc.), the t...

How to convert to date format
Hi, I received an EXCEL file that I need to manipulate in MS ACCESS and one column has a date format like this: ex: 20090220 - corresponding to Feb 20, 2009 20090131 - corresponding to Jan 31,2009 How do I format them so it can be converted to MM/DD/YYYY as I need this format to be able to import and manipulate the column in ACCESS. Right now, when I import the EXCEL file to ACCESS the data type for this column is DOUBLE , and when I tried to change the date type to DATE/TIME, it deletes the column. Appreciate your help. Thanks in advance. Correction to my post... the data type...

#Name? error
I have the following table to convert month names to french. Mth_Name Field 1= Month Number Field 2= Month Name example: 1 janvier 2 fevrier .... 12 decembre I have a query, QryCurrentMonth which uses DatePart("m",Now()) to return the current month number and find Month Name. My problem is this. When creating a report I need my date to be displayed as: "le 6 fevrier 2008". I have tried many options in my control source but to no avail. ="le " & " " & DatePart("d",Now()) & " " & [QryCurrentMonth]![MONT...

Time on messages is an hour off
Messages that I send and receive are dated one (1) hour earlier than they were actually sent and received. Even messages I send to myself have this error. My system clock and the system clock of the mail server are both synchronized to the correct time. Any ideas on what the problem could be? I checked the time zone and "adjust for daylight savings time" settings under Tools>Options>Calendar Options>Time Zone, and they are both correct as well. ...

First Time Opening Outlook
I just installed Outlook and when I try to open it, I get a popup message titled "Connecting to Microsoft Exhange Server". The message is, "Your Microsoft Exchange Server is unavailable." and a second popup says, "Configuring Outlook Accounts". The configuation process never does complete. I can even open outlook. Help! Thanks Ross Ross <Ross@discussions.microsoft.com> wrote: > I just installed Outlook and when I try to open it, I get a popup > message titled "Connecting to Microsoft Exhange Server". The message > is, "You...