#### Date and time calculations

Hello,

I have a problem with a formula in wich I like to add up an amount of
hours to a date/time value.

Please find below an example of what I am trying to do.

Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm)
Value A2: 5 (hours)
In cel A3 I would like to get the value of A1+A2 (result 16/03/2005
03:00)

Can someone suggest what formula I must use?

Thanx very much!
Robert
 0
3/29/2005 1:28:05 PM
excel 39879 articles. 2 followers.

2 Replies
536 Views

Similar Articles

[PageSpeed] 31

=a1+(a2/24)

Format the way you like.  (5 hours is 5/24ths of a day.)

Robert wrote:
>
> Hello,
>
> I have a problem with a formula in wich I like to add up an amount of
> hours to a date/time value.
>
> Please find below an example of what I am trying to do.
>
> Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm)
> Value A2: 5 (hours)
> In cel A3 I would like to get the value of A1+A2 (result 16/03/2005
> 03:00)
>
> Can someone suggest what formula I must use?
>
> Thanx very much!
> Robert

--

Dave Peterson
 0
ec357201 (5290)
3/29/2005 1:32:18 PM
The formula you are using is correct, the error is in the cell with the 5
hours in it. - You can't just type 5 and expect excel to know it is hours -
if you type 5:0:0 in the cell, you should see your desired result.

"Robert" <p.strijbosch@chello.nl> wrote in message
> Hello,
>
> I have a problem with a formula in wich I like to add up an amount of
> hours to a date/time value.
>
> Please find below an example of what I am trying to do.
>
> Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm)
> Value A2: 5 (hours)
> In cel A3 I would like to get the value of A1+A2 (result 16/03/2005
> 03:00)
>
> Can someone suggest what formula I must use?
>
> Thanx very much!
> Robert

 0
nigel8799 (11)
3/29/2005 10:26:49 PM

Similar Artilces:

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

Daylight Savings Time Changes
Hello, Does anyone know if the patch for Exchnage 2003 identified in KB926666 has to also be applied to every server that is running the Exchange Tools (System Manager)? Thanks in advance K On Wed, 31 Jan 2007 10:24:03 -0800, Kerry <Kerry@discussions.microsoft.com> wrote: >Hello, > >Does anyone know if the patch for Exchnage 2003 identified in KB926666 has >to also be applied to every server that is running the Exchange Tools (System >Manager)? > >Thanks in advance >K Im all for consistency. If the patch isnt needed, it shouldnt install, so I would at ...

Calculated Field in form to table
Can you take a calculated field from a form and pull it in to a table?? ...

No calculation on new machine
I run XP Home with Office 2002 I have just installed my programs to a new machine. Excel is behaving differently on the new machine: Observed so far: i) I cannot select a cell by moving with the arrow keys. ii) I can insert a calculation, SUM for instance, into a cell and I get a 0.00 result. iii) Format > Cells provides three options, Border, pattern and protection. The old machine additionally gave me number, alignment and font. What have I miised? Thanks, Robin Chapple Hi Robin, Check your status bar that you do not have SCRL on the right side to indicate Scroll Lock (there i...

table and form not calculated in synch
Help says to open a piviot table but I am having trouble making my columns add up to total for each indivudual ID. The totals are being calculated only in th e form view which does not up date the original table and does not transfer to a report. How can I get a feild to calculate specific feilds so that I can run these reports? ...

Help with Calculation #2
Hi, I have the following XLS with 3 sheets: Credits, Inventory & Debits. Credits sheet (Stock sold) columns: 'Product Code', 'Product' (Text field) & 'Quantity' Inventory fields: 'Product' (Text field), 'Company', 'Supplier', 'Product Code', 'Quantity', 'Cost Price' & 'Retail Price'. Debits Sheet (Stock bought) columns: 'Product Code', 'Product' (Text field), 'Quantity' & 'Total cost'. New stock entered as 'Product Code', 'Product', 'Quantity&#...

Calculate from another sheet
If i in a sheet have a1=1 , b1 =2 , c1 = 3 , d1 = 4, e1 = 5 and so on and in a2 = 100, B2 = 100, c2 = 100 , e2 =100 ans o on Then in another sheet i write maybe 4 then i want a cell there have a sum from 1 to 4 In this case from a2 to e2 if I write maybe 2 The a sum from a2 to b2 Can i do this? If 4 is A2 to E2, and 2 is A2 to B2, presumably 3 is A2 to about half way between C2 and D2? It sounds as if you want the OFFSET function; details in Excel help. -- David Biddulph "alvin Kuiper" <alvinKuiper@discussions.microsoft.com> wrote in message new...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

Outlook locks up all the time
This is a shot in the dark. Ever since getting my new Thinkpad with Windows XP, Outlook has never seemed to work properly. I've run diagnostics on my machine and nothing comes up as a problem. I've installed a spyware program because after talking to IBM tech support, they suggested I try that. I also talked to another support tech, who suggested I reinstall Windows completely. I'm not a real technical person. Won't a re-install wipe out my files? Most of the time that I use Outlook for my email, it slows down and eventually locks up. When I restart my computer,...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

Date Format when incoming date can be 0
I have a date fiels yyyymmdd which can have a value of 0 or a valid date in recent past. Excel displays the 0 date as 01/01/1900 and the non zero dates as expected. Cant figure this out , any help would be appreciated. Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending on your options) as the date value for Example 6/3/2005 is 38506 using the 1/1/1900 if the cell is formatted as a date it is responding with the date it thinks you mean. If you want the cell to display a zero =if(Date=0,"0",date) "DP NY10601" wrote: > I have a date fiels y...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

how excel calculates the major units on an axis when MajorUnitIsAuto property set to true #2
I need to know, how excel calculates the major units on an axis when MajorUnitIsAuto property set to true. We are porting an existing excel chart application to .net with help of a third party tool for charting. If MajorUnitIsAuto property set to true, excel automatically calculates the major unit.Need to know what algorithm excel uses to calculate the Major Units. Thanks in advance, Mathew Microsoft has posted an article which tells how the minimum and maximum scale values are determined, in terms of this mysterious major unit, but I have seen nothing about how this major unit is calcula...

how do I take a sub total field and calculate sales tax to it
trying to take a total cell. and add sales tax to it. Hi Michael With Total in A1` and Sales tax in B1 e.g. 7% =A1*(1+B1) -- Regards Roger Govier "Michael Beck" <Michael Beck@discussions.microsoft.com> wrote in message news:2C1017DC-E037-4388-855F-07946472C9A5@microsoft.com... > trying to take a total cell. and add sales tax to it. ...

download the Credit Card transaction date not the the posting date
Then I download a statement from a Credit card and import it in to MS Money I always get the Posting Date not the Transaction date. Do I need another column in the register, help does not tell me how to do that? Or is the only solution changing each entry by hand? ( that doesn't make sense) This credit card offers downloads in either QIF or spreadsheet. In microsoft.public.money, riskymanr wrote: >Then I download a statement from a Credit card and import it in to MS Money I >always get the Posting Date not the Transaction date. Do you mean you first enter the transactions by...

Total Cost Calculation for MS Project 2007
Good Afternoon, Here is the scenario/problem: I have created a task (100 days duration) and assigned 2 resources to the task at a straight time rate of 22 per hour. One resource (A) has a max unit of 100% which is assigned at 100% the total cost according the resource sheet is 22,000. Resource (B) is at the same pay rate, same assignment level 100% but has a max units % of 400% (Indicating 4 group resources assigned at 100%. The total cost according the resource sheet still reads 22,000. How can that be?? Am I not understanding something or am I missing a step? Recap: ...

How do I convert a time format to a double
I use a function to calculate the hours worked based on time 12:00pm to 4:00pm is 4.0 hours when I get the number for hours worked it is in time format / .27398237 I need the number 4 so that I can do my calculation. How do I convert it to a number? Multiply with 24 and format as general -- Regards, Peo Sjoblom "DMB" <DMB@discussions.microsoft.com> wrote in message news:552FAD83-E8D4-4573-B9FF-613DDD259C22@microsoft.com... > I use a function to calculate the hours worked based on time > > 12:00pm to 4:00pm is 4.0 hours > > when I get the number for hou...

If Formula , Adding Days to a Date
A B 1 06/06/10 I want to make a formula that calculate as following; in Cell (B1) if the date in future the result will be [Enrolled], if not it will be Cell (A1) + 730 Days On Sat, 27 Mar 2010 02:14:01 -0700, Khalid A. Al-Otaibi <KhalidAAlOtaibi@discussions.microsoft.com> wrote: > A B >1 06/06/10 > >I want to make a formula that calculate as following; > >in Cell (B1) if the date in future the result will be [Enrolled], if not it >will be Cell (A1) + 730 Days Try this formula in cell B1: =IF(A1>TODAY(), "[En...

lookup a date from an array of date ranges if conditions are met
Please help...i need to lookup up a value on a specific date range. for example: column A: column b (from 9/01/2003 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Sounds like autofilter could be something, select the data (should have headers), do data>filter>autofilter, filter on the date column using custom and gate than or equal to and put 09/01/03 as a criteria -- Regards, Peo Sjoblom "nscanceran" <nscanceran.wj1qn@excelforum-no...

Release Date of V 4.0
Hi Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 be available for partners before it is available for the general public? Thanks alot ...

in VBA how to retreive cell values 1 byte at a time?
How would I write VBA to retrieve (get at) the value in a cell byte by byte from its natural form? i.e. no conversions are to be performed. E.g., if a string, then I may get more bytes than for a number. Thanks. Penny, If the cell contains text, you can retrieve characters (bytes) with the MID function. =MID(A2, CharPosition, 1). You can get the actual byte values with the CODE function =CODE(MID(A2, CharPosition, 1)). If a number, you can extract the decimal digits of the number in the same way, but you're not getting actual bytes (as stored by Excel). Excel stores numbers in ...