the other lines are still a mistery to me. 1 Answer Sorted by: 1 DAYS (date2 date1) Calculates the difference, in days, between two date values. Normally, the calculation should work if the cells A2 and B2 are formatted like NUMBERS I did that and I receive the same error. but, I understood the first 2 lines from your post No, number format "DD HH" shows the day part of day #2 which is in Calc because day zero is. =(A1-B1)*24 formatted as plain decimal number this gives 2.25*24 =>54 The same value is shown as 06:00 with number format HH:MM because the normal time format disregards the integer part of the number (the 2 days). The same value is shown as 54:00 with number format :MM =A1-B1 gets the difference in days and #VALUE if there is text in the calculation:Ģ.25 as unformatted number if the difference is 2.25 (2 days and 6 hours)
Villeroy wrote:Cell formatting is completely irrelevant as long as your values are numbers. My problem goes like this I would like to be able to calculate the difference in time between 2 different dates more specific, I would like to calculate the number of hours let's say between the Offer Request I receive and the Answer I send to the customer those are my "datas":Ĭolumn A ( offer request time ) | Column B ( sent answer time )Ĭell format type: date, format DD.MMM.YYYY HH:MM:AM/PM | date, format DD.MMM.YYYY HH:MM:AM/PMĬell display form: 14.Oct.2009 16:00:PM | 15.Oct.2009 10:30:AMĬan someone please let me know what formula should i use in order to calculate the difference between those dates ? For now, I must say that I do not specially care about the form of the result meaning that it can be calculated in hours form ( like 38 hours for example )or like day form ( 1day: 2 hours: 36 minutes for example ) anyway any suggestion will be highly appreciated what I get when trying to make the pure difference between 2 dates ( = B2-A2 ) is #VALUE! - and the status bar information is "" Error: Wrong data type" I format the cells in both number and date format and have the same result I tried a function like =Now()-cell and received a value but hard to understood I tried DAY and DATE functions but with the same result ( Error ) what I think is that there is more like a combination of functions that should be used and I did not liked the math at all during my school I like to mention that I did some research on the forum and didn't fount out quite what I expected as a solution however I did tried some solutions without any luck
Openoffice calculate date difference software#
All the other dates are once added, once subtracted.I am using OOo 3.0 on Ubuntu 9.04 - Jaunty and I come across to a point when I need some support for the CALC software And note that the formula above can be simplified as follow: A789 - A2. As dates are represented by numbers, the difference between two dates is equal to the subtraction of two numbers. If your concern is to have only one formula that does all the calculation in one go, you can use =SUMPRODUCT(A3:A789-A2:A788) which will calculate (A3-A2) + (A4-A3) + (A5-A4) +. You can then make a SUM(B3:B789) to get the total of days. Or select cell B3 and click with your mouse on the black box in the bottom right corner of cell B3, then drag down the formula up to your last row (keep the left mouse button pressed down) and then release the button. If your concern is to copy a formula down column B, then you can do it very easily in at least two ways: Select the cell B3, press Ctrl-C (or menu Edit > Copy), then select the range B4:B789 (replace 789 by your last row you want to copy to), then press Ctrl-V (or the menu Edit > Paste).
Not sure I have fully understood your problem, but let me try to help you!
0 kommentar(er)
