[Solved] Need help with DateDiff

Discuss the spreadsheet application

[Solved] Need help with DateDiff

Postby Curious Onlooker » Thu Jan 02, 2020 10:09 am

Hello everyone.

I need a hand with DateDiff
The manual and function list state DATEDIF(StartDate,EndDate,mode)

I can't find anywhere that specifies what Mode actually is?
I've tried various things such as [hh] [dd] etc, but get no joy.

I'm actually after the number of hours between the two dates, both of which are in 244 hour format, eg., 04/12/2019 18:00:00

Appreciate any pointers
Last edited by MrProgrammer on Sun Jan 12, 2020 5:40 am, edited 1 time in total.
Reason: Tagges ✓ [Solved]
OpenOffice: 2018 (rev 972.1032) 64-bit; Windows
Curious Onlooker
 
Posts: 1
Joined: Thu Jan 02, 2020 10:05 am

Re: Need help with DateDiff

Postby robleyd » Thu Jan 02, 2020 10:57 am

There isn't a DATEDIF function in OpenOffice Calc. Your signature shows OpenOffice: 2018 (rev 972.1032) 64-bit on Windows - there is no Windows 64 bit AOO.

Perhaps you have LibreOffice, for which the LO Help (6.0.7.3) tells us:

DATEDIF
This function returns the number of whole days, months or years between Start date and End date.
Syntax
DATEDIF(Start date; End date; Interval)
Start date is the date from when the calculation is carried out.
End date is the date until the calculation is carried out. End date must be later, than Start date.
Interval is a string, accepted values are "d", "m", "y", "ym", "md" or "yd".
Value for "Interval"
Return value
"d" Number of whole days between Start date and End date.
"m" Number of whole months between Start date and End date.
"y" Number of whole years between Start date and End date.
"ym" Number of whole months when subtracting years from the difference of Start date and End date.
"md" Number of whole days when subtracting years and months from the difference of Start date and End date.
"yd" Number of whole days when subtracting years from the difference of Start date and End date.

Example
Birthday calculation. A man was born on 1974-04-17. Today is 2012-06-13.
=DATEDIF("1974-04-17";"2012-06-13";"y") yields 38. =DATEDIF("1974-04-17";"2012-06-13";"ym") yields 1. =DATEDIF("1974-04-17";"2012-06-13";"md") yields 27. So he is 38 years, 1 month and 27 days old.
=DATEDIF("1974-04-17";"2012-06-13";"m") yields 457, he has been living for 457 months.
=DATEDIF("1974-04-17";"2012-06-13";"d") yields 13937, he has been living for 13937 days.
=DATEDIF("1974-04-17";"2012-06-13";"yd") yields 57, his birthday was 57 days ago.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3506
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need help with DateDiff

Postby Lupp » Thu Jan 02, 2020 1:02 pm

In addition you should note that the "international default" separator for function parameters is the semicolon for OpenOffice successors. LibreOffice some time ago introduced the comma to the role in locales defaulting the decimal separator to the point. This aimed at "better" compatibility with a competitors spreadsheets jettisoning compatibility with LibO itself across locales. Evil idea. However, they also introduced a setting for Calc under 'Formula'>'Separators'>'Function:' where informed users can and should enter the semicolon. LibO also still accepts the semicolon for input of formulas under any locale. If you want to continue to take part in the international ("en") community, please also do so. Otherwise you may consider to shift to an Excel forum.
Your generalized formula should thus read:
Code: Select all   Expand viewCollapse view
DATEDIF(StartDate; EndDate; mode)

Concerning the 'mode' parameter "robleyd" already gave you the needed information.

Now you may test the function with the formula
Code: Select all   Expand viewCollapse view
=DATEDIF(B1;C1;"y") & " years " &  DATEDIF(B1;C1;"ym")& " months " &  DATEDIF(B1;C1;"md") & " days"
and the values
Code: Select all   Expand viewCollapse view
2022-03-29 and 2062-01-11
for StartDate and EndDate respectively in B1:C1. I suppose that's what many users want to get.
Please note that the result NOT is a way to express actual durations. Due to the purely calendaric meaning of dates you cannot calculate a number of days (14533 for the given example) from the split result. Simply use
Code: Select all   Expand viewCollapse view
=EndDate - StartDate
for a duration in days.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2929
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Bill and 20 guests