[Solved] Calculate the time difference between 2 diff dates

[needhelp]

Hi there fellows;

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;
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;

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":

Column A ( offer request time ) | Column B ( sent answer time )
Cell value: 10/14/2009 16:00:PM | 10/15/2009 10:30:AM
Cell format type: date, format DD.MMM.YYYY HH:MM:AM/PM | date, format DD.MMM.YYYY HH:MM:AM/PM
Cell display form: 14.Oct.2009 16:00:PM | 15.Oct.2009 10:30:AM

Can 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;

So, please bare with me on this one;

[tn@BeWo]

Hi 'needhelp'
I doubt that you have dates in those cells. Is 16:00:PM a valid time in your locale? Or should it be 16:00 PM?
Also: 10/14/2009 doesn't match your format pattern DD.MMMM.YYYY - if it was a date, you should see 10.October.2009

[Villeroy]

Cell formatting is completely irrelevant as long as your values are numbers.
=A1-B1 gets the difference in days and #VALUE if there is text in the calculation:
2.25 as unformatted number if the difference is 2.25 (2 days and 6 hours)
The same value is shown as 54:00 with number format [HH]:MM
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 difference in unit hours is:
=(A1-B1)*24 formatted as plain decimal number this gives 2.25*24 =>54

No, number format "DD HH" shows the day part of day #2 which is 1900-01-01 in Calc because day zero is 1899-12-30. So that format shows 2.25 as "01 06"

[tn@BeWo]

Cell formatting is completely irrelevant as long as your values are numbers.

Sure, but as the appearance doesn't match the format, I've the suspicion that the cells don't contain numbers but text, making them invalid type for substraction.

[Villeroy]

Cell formatting is completely irrelevant as long as your values are numbers.

Sure, but as the appearance doesn't match the format, I've the suspicion that the cells don't contain numbers but text, making them invalid type for substraction.

Surely there is text involved. Next question would be: How get the text values into the spreadsheet? In most cases this is a well known Excel issue.

[needhelp]

Hi 'needhelp'
I doubt that you have dates in those cells. Is 16:00:PM a valid time in your locale? Or should it be 16:00 PM?
Also: 10/14/2009 doesn't match your format pattern DD.MMMM.YYYY - if it was a date, you should see 10.October.2009

Hi there th@BeWo;
What I see on the cell is indeed: 14.Oct.2009 .......the format I use is DD.MMM(only 3 M's).YYYY;
The format code I have for the time is indeed .......HH:MM AM/PM ....which is display as .....16:15:PM for example .....

Cell formatting is completely irrelevant as long as your values are numbers.
=A1-B1 gets the difference in days and #VALUE if there is text in the calculation:
2.25 as unformatted number if the difference is 2.25 (2 days and 6 hours)
The same value is shown as 54:00 with number format [HH]:MM
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 difference in unit hours is:
=(A1-B1)*24 formatted as plain decimal number this gives 2.25*24 =>54

No, number format "DD HH" shows the day part of day #2 which is 1900-01-01 in Calc because day zero is 1899-12-30. So that format shows 2.25 as "01 06"

Oh man ....I should probably stop drinking ...but, I understood the first 2 lines from your post
Normally, the calculation should work if the cells A2 and B2 are formatted like NUMBERS; I did that and I receive the same error ...maybe I'm doing this wrong ........the other lines are still a mistery to me .......maybe you can explain this in ....."english"...

PS: As I know, the text is marked with a " ' " sign which is showed only on the "operation area"; I do not have such a mark so I do not have text on my cells .....as I see-it .....

regards,

[tn@BeWo]

I do not have such a mark so I do not have text on my cells .....as I see-it ...

... wrong. The leading single quote ensures to have text, which could be interpreted as a number (in your case a number that is meant as a date), as text (and not converted to a number) in a cell.
You do have text in your cells and that text will now not be converted into a number anymore.
Delete the cells and start over, entering valid dates into virgin cells. You might want to postpone that until tomorrow.

[needhelp]

Hmmm ..i must apologize here...indeed I had text on those cells .......without a " '" mark ...but text ...I can swear I saw the 10/14/2009 format but ..........somehow the CALC decided for me that the value there is plain old text and nothing else .......however ....


Now I have :
column A ........... | Column B
Value: 10/13/2009 06:28:00 | 10/14/2009 09:38:00
Display: 13.Oct.2009 06:28 PM | 14.Oct.2009 09:38 AM

The difference =B2-A2 gives me = 03:10:00 PM ...which is the exact difference between 9:38 and 6:28 but without taking into account the day ......how can I have the "day" parameter taken into account ????

I do not have such a mark so I do not have text on my cells .....as I see-it ...

... wrong. The leading single quote ensures to have text, which could be interpreted as a number (in your case a number that is meant as a date), as text (and not converted to a number) in a cell.
You do have text in your cells and that text will now not be converted into a number anymore.
Delete the cells and start over, entering valid dates into virgin cells. You might want to postpone that until tomorrow.

I'm still sober tn@BeWo .....so ....bring -it -on baby ......

[Villeroy]

2.25 is shown as 54:00 with number format [HH]:MM
INT(2.25) => 2 (number of days)
MOD(2.25;1) => 0.25 (06:00am)
There is no number format to show both numbers in one value.
=INT(B2-A2)&" Days and "&TEXT(MOD(B2-A2;1);"HH:MM") gives a textual representation of the result.

A value is text if it does not evaluate in the respectice number format language (NFL "Default" defaults to the application locale). 10/14/2009 16:00:PM and 10/15/2009 10:30:AM require US-English number format locale (M/D/Y).

A value is forced to be text when entered into a cell with number format "@" (Text).
A value is forced to be text when entered with a leading apostrophe into a cell with any number format except for "@"

[tn@BeWo]

[OT]

I'm still sober tn@BeWo .....so ....bring -it -on baby ......

that's been your interpretation of what I wrote - but it gave me a good laugh, said to be healthy, and that was what the joke's about
[/OT]

The difference =B2-A2 gives me = 03:10:00 PM ...which is the exact difference between 9:38 and 6:28 but without taking into account the day ......how can I have the "day" parameter taken into account ????

The days are taken into account (and there's nothing like a parameter for it), you just missread the result because of the poor formatting and the peculiar preference for the 12h clock (which makes sense on the wrist but not in computing). If the days hadn't been accounted for, you would see ―08:50
03:10:00 PM means 15 hours and 10 minutes. Use an explicit format (either number or 24h-date) to view differences.

[needhelp]

OK tn@BeWo;

I got that result; so please help me with this one;
Is it make sense to change the Am/PM format .??....except that I cannot understand how CALC is going to do the math if it does not know if it's 3 in the morning or in the afternoon but anyway this is the least of my problem......

And second > how to format the cell to give me 15 hours and 10 minutes; I formatted the cell as General, Number, date and time and found no reasonable result;

[tn@BeWo]

Ah, come on - AM/PM is for me just a bit more peculiar than inches, ounces, gallons and all your other unwieldy units. No need to take that seriously. (And it poses inconveniences for you, too.)

As for your time difference:
Right-click the cell, choose Format Cells ... in the dialog click the 'Numbers' tab.
You'll see that the automatically chosen Category is Time and the Default Format Code (in the editable field below) shows
HH:MM:SS AM/PM
Enter that field, delete the ' AM/PM' part, acknowledge your choice, and enjoy.

(You can, but probably you wouldn't want to, display the number as number (of days). With Category Number, Decimal places 2, Leading zeros 1, you'd get it being displayed as 0.63)

[needhelp]

Ok, I'll try this and let you all know if it's suited for my case;
Thanks

[mriosv]

Why not upload the file, deleting other confidential information, and so all can see what are in the cells.

[needhelp]

INDEED, it worked like a charm;
Thank you all and have a nice debugging/support time

mriosv - thanks man; the provided answers were OK; only that not being so familiar with CALC, rather than Excel . . ..I did not understood some things; but, again, the last answer, which was more than explicit did the job perfectly;

Regards, to you all;

[prasadd929]

Hi,

First change date format into timestamp like dd-mm-yyyy hh24:mi:ss
then for time difference appply
TRUNC((DAYS(M2;L2)*86400)/3600).....................................hours..in cell 1
TRUNC(MOD(DAYS(M2;L2)*86400 ;3600)/60)........................minutes..in cell 2
MOD(MOD(DAYS(M2;L2)*86400;3600);60)............................seconds..in cell 3

where M2 and L2 are celll containing timestamp values (m2>l2)


<ch maruthi prasad>