[Solved] Calculate the time difference between 2 diff dates

Discuss the spreadsheet application

[Solved] Calculate the time difference between 2 diff dates

Postby needhelp » Wed Oct 14, 2009 7:53 pm

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;
Last edited by needhelp on Wed Oct 21, 2009 9:17 am, edited 2 times in total.
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Postby tn@BeWo » Wed Oct 14, 2009 8:02 pm

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
Last edited by tn@BeWo on Wed Oct 14, 2009 8:05 pm, edited 1 time in total.
OOo 3.2.1 on ubuntu 10.4, MsWXP, MsW2k
tn@BeWo
Volunteer
 
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Postby Villeroy » Wed Oct 14, 2009 8:05 pm

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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Postby tn@BeWo » Wed Oct 14, 2009 8:08 pm

Villeroy wrote: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.
OOo 3.2.1 on ubuntu 10.4, MsWXP, MsW2k
tn@BeWo
Volunteer
 
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Postby Villeroy » Wed Oct 14, 2009 8:51 pm

tn@BeWo wrote:
Villeroy wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Postby needhelp » Wed Oct 14, 2009 8:52 pm

tn@BeWo wrote: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 .....

Villeroy wrote: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,
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Postby tn@BeWo » Wed Oct 14, 2009 9:14 pm

needhelp wrote: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. ;)
OOo 3.2.1 on ubuntu 10.4, MsWXP, MsW2k
tn@BeWo
Volunteer
 
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Postby needhelp » Wed Oct 14, 2009 9:27 pm

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

tn@BeWo wrote:
needhelp wrote: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 ......;)
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Postby Villeroy » Wed Oct 14, 2009 11:49 pm

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 "@"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Postby tn@BeWo » Thu Oct 15, 2009 12:12 pm

[OT]
needhelp wrote:I'm still sober tn@BeWo .....so ....bring -it -on baby ......;)
:lol: 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]

needhelp wrote: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.
OOo 3.2.1 on ubuntu 10.4, MsWXP, MsW2k
tn@BeWo
Volunteer
 
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Postby needhelp » Thu Oct 15, 2009 7:15 pm

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;
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Postby tn@BeWo » Thu Oct 15, 2009 9:42 pm

Ah, come on - AM/PM is for me just a bit more peculiar than inches, ounces, gallons and all your other unwieldy units. :roll: 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)
OOo 3.2.1 on ubuntu 10.4, MsWXP, MsW2k
tn@BeWo
Volunteer
 
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Postby needhelp » Sat Oct 17, 2009 11:54 am

Ok, I'll try this and let you all know if it's suited for my case;
Thanks
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Postby mriosv » Sat Oct 17, 2009 5:16 pm

Why not upload the file, deleting other confidential information, and so all can see what are in the cells.
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
mriosv
 
Posts: 650
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Calculate the time difference between 2 different dates

Postby needhelp » Wed Oct 21, 2009 9:11 am

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

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;
needhelp
 
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: [Solved] Calculate the time difference between 2 diff da

Postby prasadd929 » Mon Apr 12, 2010 8:44 am

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>
OpenOffice 3.1
prasadd929
 
Posts: 2
Joined: Mon Apr 12, 2010 8:17 am
Location: ETL Developer


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests