[Solved] Calculate the time difference between 2 diff dates

Discuss the spreadsheet application
Post Reply
needhelp
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

[Solved] Calculate the time difference between 2 diff dates

Post by 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;
Last edited by needhelp on Wed Oct 21, 2009 9:17 am, edited 2 times in total.
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
tn@BeWo
Volunteer
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Post by 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
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Post by 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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tn@BeWo
Volunteer
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Post by tn@BeWo »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
needhelp
Posts: 6
Joined: Wed Oct 14, 2009 7:23 pm

Re: Calculate the time difference between 2 different dates

Post by needhelp »

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
tn@BeWo
Volunteer
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Post by tn@BeWo »

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

Re: Calculate the time difference between 2 different dates

Post by 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 ????
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculate the time difference between 2 different dates

Post by 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 "@"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tn@BeWo
Volunteer
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

Post by tn@BeWo »

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

Re: Calculate the time difference between 2 different dates

Post by 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;
OpenOffice 3.0
Ubuntu 9.04 - Jaunty
tn@BeWo
Volunteer
Posts: 253
Joined: Fri Jul 24, 2009 8:26 am

Re: Calculate the time difference between 2 different dates

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

Re: Calculate the time difference between 2 different dates

Post by needhelp »

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
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Calculate the time difference between 2 different dates

Post by mriosv »

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

Re: Calculate the time difference between 2 different dates

Post by needhelp »

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

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

Post by 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>
OpenOffice 3.1
OBobbyBoy
Posts: 1
Joined: Thu Jul 13, 2017 11:47 pm

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

Post by OBobbyBoy »

Actually, the simple way is like this:
Use the normal time format (default) for the displayed time cells including AM or PM.
In the receiving field for the time difference place this formula: =(C5<B5)+C5-B5 (C5 is the later time)
Format the receiving cell to HH:MM (13:37).
The result is in hours and minutes of difference. It works across different dates (if date is in the original fields).
Done
:D
Bob Nesbit
Open Office 5.3.1.2 (x64)
onWindows 10 Pro 1703
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

OBobbyBoy wrote:Actually, the simple way is like this:
Use the normal time format (default) for the displayed time cells including AM or PM.

Formatting has no influence on the calculation result. Even if you format your formula cell as a currency, percent or fraction, the result will be the same and you can change the appearance (formatting) at any time. 0.5 is the exact same value as 12 AM, 50%, 0,50€, $0.50, ½ or 1899-12-30 12:00:00
In the receiving field for the time difference place this formula: =(C5<B5)+C5-B5 (C5 is the later time)

The formula adds one day if C5 is smaller (earlier) than B5. In this case you get the time span between the time in B5 and next day's time in C5. This is useful in many cases but you have to understand what the formula actually does, e.g. that it can not return any result >=1 day if both times represent cell values between 0 and 1
Format the receiving cell to HH:MM (13:37).

This format works fine in this particular case since we never get any time span beyond 24 hours due to the above formula. As a rule of thumb, time formattings representing a time span should enclose the hours in brackets: [HH]:MM displays 27 hours as 27:00. HH:MM would show 03:00 which is a clock time wrapping over the full day. As always, the cell value remains the same 1.125 (one day and one eighth of a day)
The result is in hours and minutes of difference. It works across different dates (if date is in the original fields).
Done
:D
Actually, the result is the difference in days as you can see by removing the format. All dates in all spreadsheets are day numbers (1899-12-30 is day zero). All times in all spreadsheets are fractions of those day numbers. Spreadsheets calculate times in unit "days".

P.S. and since the problem of this topic originated from text values, your first post is not only imprecise but off topic. At the time when starting this thread, Calc could not subtract any text values. Meanwhile Calc can subtract numeric strings if they represent integer values or ISO dates and times ("1234", "2017-07-12", "13:45:59"). Any other type of numeric string could be ambivalent.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply