Re: [libreoffice-users] date, day number, week number in one cell
At 09:04 12/07/2018 -0400, Carl Winerich wrote: Existing cell (for the month of February) has the following formula; =$G30-$H30+$D32+$E32+COLUMN(A5) That stuff points to various parts of the spreadsheet and returns the correct numeric day number of the month. When I try to concat it with, say, =DAYS then Err:510 appears in the cell. I'm not surprised: you concatenate references or expressions in a formula, not multiple formulae. What's that extra equals sign doing there? That's like writing =A1+=B2 instead of =A1+B2. Any ideas? Yes: excise that superfluous equals sign. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
Solved the problem. All I had to do was the following; Format the selected calendar cell as follows; DD (then a bunch of spaces) WW The result returned was the correct calendar date number and the current week of the year. Now all I have to figure out is how the get the day-number-of-the-year in there. Thank you, all, for your efforts. Carl On 07/12/2018 04:12 AM, Brian Barker wrote: > At 19:30 11/07/2018 -0400, Carl Winerich wrote: >> It's back to the same old question; ... > > Indeed so - and the answer is very much the same as on the previous > two occasions you have asked this. > >> In a calendar, how can I put the day's date (number only) and week >> number and day-number-of-the-year in one cell? > > To put multiple items in one cell, you must concatenate the individual > values. You can do this using the CONCATENATE() function: > =CONCATENATE(;;) > or probably more easily using the & operator: > =&& > - where , , and represent your items - references or > formulae. > > Note that you will have to take care of spacing, so you may need > something like > =&" "&&" "& > instead. > > Note that concatenation - expressed either way - both requires and > produces text items. If you pass it numeric values, these are > converted to text automatically on the fly. If you want more control > over exactly how a value is represented, you may want to do the > conversion yourself, using the TEXT() function, which allows you to > specify the format of the converted value. So if you wanted, say, all > your dates to appear as two-digit numbers, so the first of the month > was "01" instead of just "1", your formula might start > =TEXT(;"00")&" "& ... > >> Particularly in cell A5, B5...etc which are all cells that contain >> only the day number of the month. Here's a link to the Calc calendar >> template I'm trying to use- >> https://extensions.libreoffice.org/templates/calendar-creator The >> template is very useful and I don't want to interfere or mess up the >> calendar creation - which it does perfectly. > > You'll have to puzzle out how to derive formulae for the required two > new values for yourself, unless the answers are buried somewhere in > the existing template. The functions WEEKNUM(), WEEKNUMADD(), and > DAYS() may help. You can read the existing formulae in your template > by selecting relevant cells and looking in the Input Line for each, of > course. > > I trust this helps. > > Brian Barker > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculate difference between two dates time)
Krunose wrote: Hi, just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18. 22:20, why then =TEXT((A2-A1);"d:h:m") returns 30:6:50 instead 0:6:50? Assuming those dates are 7th January 2018, since your mail headers indicate you're using a US locale... 2018-01-07 15:30:00 is represented internally as 43107.645833 2018-01-07 22:20:00 is represented internally as 43107.930556 Subtracting those numbers gives 0.284723, which represents the date/time 1899-12-30 06:50:00 The day of month is 30, hence the 30 when that value is represented as d:h:m. -- Mark. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculate difference between two dates (with time)
12.07.2018 u 16:36, Michael D. Setzer II je napisao/la: 01/17/18 03:30 PM 01/17/18 03:30 PM 01/17/18 10:20 PM 01/17/18 10:20 PM 0:6:50:0 0:06:50:00 Formula in A3 "=TEXT(INT(A2-A1),"#0")&":"(((A2-A1)-INT(A2-A1)),"H:M:S") Nice! Thanks! But makes me wonder what's wrong with =TEXT((A2-A1);"d:h:m") as it works well in Excel. Maybe I should file a feature request? Kruno Formula in B3 "=TEXT(INT(A2-A1),"#0")&":"(((A2-A1)-INT(A2-A1)),"HH:MM:SS") I did include seconds, but you could remove the :S, and have options to show 2 digits or just one. The #0 makes it print the 0, but ## would print blank for zero values. On 12 Jul 2018 at 13:42, Krunose wrote: To: users@global.libreoffice.org From: Krunose Subject:[libreoffice-users] Calculate difference between two dates (with time) Date sent: Thu, 12 Jul 2018 13:42:36 +0200 Hi, just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18. 22:20, why then =TEXT((A2-A1);"d:h:m") returns 30:6:50 instead 0:6:50? Thanks, Kruno -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy ++ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ ++ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS ROSETTA 65602903.467560 | ABC 16613838.513356 SETI109419383.050719 | EINSTEIN141198856.499240 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] date, day number, week number in one cell
Again, it stripped all the spaced?? So, there it is with spaces changed to .
Date...Month.Day.Weekday.Julian.week.of.year
01/01/18...1.1...Monday...1...1
01/02/18...1.2...Tuesday..2...1
01/03/18...1.3...Wednesday3...1
On 13 Jul 2018 at 1:37, Michael D. Setzer II wrote:
From:Michael D. Setzer iimsetze...@gmail.com
To:Carl winerichl...@ipadring.net,
users@global.libreoffice.org
Date sent:Fri, 13 Jul 2018 01:37:48 +1000
Subject:Re: [libreoffice-users] date, day number, week
number in one cell
Priority:normal
Not sure why this mailing list screws up the text. Did it in a text editor, so
assumed it could handle it without screwing up the formating to much, but it
did?
DateMonthDayWeekdayJulianweek of year
01/01/1811Monday11
01/02/1812Tuesday21
01/03/1813Wednesday31
Seems it doesn't handle tabs, so changed them to blanks.
Some other stuff got thrown in there that didn't exist in the text??
On 13 Jul 2018 at 1:20, Michael D. Setzer II wrote:
=Not fully clear on what you want, but this is what I did in a little test.
Did this with mono font, but list might change things so they don't line up,
but sould be easy to figure out.
DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1
The first date was in A15, and A16 was just A15+1Column B just had =month(A15)
to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1
One could just use the text command andto make whatever combination
one might want??
Did the dates all the way to 12/31,
On 11 Jul 2018 at 19:30, Carl Winerich wrote:
To:;users@global.libreoffice.org
From:60;Carl winerichl...@ipadring.net
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400
p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu;
}p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;
In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day/font
0; number of the month.
Heres
0; a link to the Calc calendar template Im trying to use-
https://extensions.libreoffice.org/templates/calendar-creator
The
0; template is very useful and I dont want to interfere or mess up/span
0; the calendar creation- which it does perfectly.
Thank
0; you,
Carl
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] date, day number, week number in one cell
Not sure why this mailing list screws up the text. Did it in a text editor, so
assumed it could handle it without screwing up the formating to much, but it
did?
DateMonthDayWeekdayJulianweek of year
01/01/1811Monday11
01/02/1812Tuesday21
01/03/1813Wednesday31
Seems it doesn't handle tabs, so changed them to blanks.
Some other stuff got thrown in there that didn't exist in the text??
On 13 Jul 2018 at 1:20, Michael D. Setzer II wrote:
=Not fully clear on what you want, but this is what I did in a little test.
Did this with mono font, but list might change things so they don't line up,
but sould be easy to figure out.
DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1
The first date was in A15, and A16 was just A15+1Column B just had =month(A15)
to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1
One could just use the text command andto make whatever combination
one might want??
Did the dates all the way to 12/31,
On 11 Jul 2018 at 19:30, Carl Winerich wrote:
To:;users@global.libreoffice.org
From:60;Carl winerichl...@ipadring.net
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400
p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu;
}p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;
In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day/font
0; number of the month.
Heres
0; a link to the Calc calendar template Im trying to use-
https://extensions.libreoffice.org/templates/calendar-creator
The
0; template is very useful and I dont want to interfere or mess up/span
0; the calendar creation- which it does perfectly.
Thank
0; you,
Carl
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] date, day number, week number in one cell
=Not fully clear on what you want, but this is what I did in a little test.
Did this withmono font, but list might change things so they don't line up,
but sould be easy to figure out.
DateMonthDay60;WeekdayJulianweek of year01/01/1811=Monday1;1
01/02/1812=Tuesday20;101/03/1813=Wednesday360;1
The first date was in A15, and A16 was just A15+1Column B just had =month(A15)
to get Month
Column C just had =day(A15) to get day
Column D to get day of Week
=INDEX($J$1:$J$7,WEEKDAY(A15))
$J$1:$J$7 have Sunday thru Saturday
Column E to calculate the Julian Day of Year
=A15-DATE(YEAR(A15),1,1)+1
Column F to get week of year
=INT(E15/7)+1
One could just use the text command andto make whatever combination
one might want??
Did the dates all the way to 12/31,
On 11 Jul 2018 at 19:30, Carl Winerich wrote:
To:;users@global.libreoffice.org
From:60;Carl winerichl...@ipadring.net
Subject:;[libreoffice-users] date, day number, week
number in one cell
Date sent:60;Wed, 11 Jul 2018 19:30:41 -0400
p { margin-bottom: 0.1in; line-height: 120%; }p.western { font-family:Ubuntu;
}p.cjk { font-size: 10pt; }a:link {}
Its
0; back to the same old question;
In
0; a
0; calendar, how can I put the days date (number only) and week
0; number and day-number-of-the-year in one cell?Particularly in
0; cell A5, B5...etc which are all cells that contain only the day
0; number of the month.
Heres
0; a link to the Calc calendar template Im trying to use-
https://extensions.libreoffice.org/templates/calendar-creator
The
0; template is very useful and I dont want to interfere or mess up
0; the calendar creation- which it does perfectly.
Thank
0; you,
Carl
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculate difference between two dates (with time)
01/17/18 03:30 PM 01/17/18 03:30 PM 01/17/18 10:20 PM 01/17/18 10:20 PM 0:6:50:0 0:06:50:00 Formula in A3 "=TEXT(INT(A2-A1),"#0")&":"(((A2-A1)-INT(A2-A1)),"H:M:S") Formula in B3 "=TEXT(INT(A2-A1),"#0")&":"(((A2-A1)-INT(A2-A1)),"HH:MM:SS") I did include seconds, but you could remove the :S, and have options to show 2 digits or just one. The #0 makes it print the 0, but ## would print blank for zero values. On 12 Jul 2018 at 13:42, Krunose wrote: To: users@global.libreoffice.org From: Krunose Subject:[libreoffice-users] Calculate difference between two dates (with time) Date sent: Thu, 12 Jul 2018 13:42:36 +0200 > Hi, > > just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18. > 22:20, why then > > =TEXT((A2-A1);"d:h:m") > > returns 30:6:50 instead 0:6:50? > > > Thanks, > > Kruno > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy ++ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ ++ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS ROSETTA 65602903.467560 | ABC 16613838.513356 SETI109419383.050719 | EINSTEIN141198856.499240 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] How to connect my own listener to a Form RowSet ?
Hi,
I would like to manage a Form into a pseudo Basic Object. I Writed a
pseudo object to test (Bellow). I know a Form could be used with RowSet
interface, but in the code bellow there is a problem in Sub Ecouter()
with line :
_FormRowSet.addRowSetApproveListener(_ecoute1)
The error is that _FormRowSet. doesn't support
addRowSetApproveListener() call ...
I don't know if the problem comme from the fact listeners could not be
methods of pseudo object, or if the RowSet interface into a form is not
completed ...
I know also I could clone the RowSet service of a form but it's read
only. What I'm looking for is to listen the origin rowset of my form
with read/write ...
Do you think it's possible ?
Thank you for your help !
Patrick
REM * BASIC *
Option Compatible
Option ClassModule
Option Explicit
Private _FormRowSet As variant
Private _ecoute1 As Variant
Private _ecoute2 As Variant
Private Sub Class_Initialize()
End Sub
Private Sub Class_Terminate()
End Sub
Rem
***
Rem
Rem PROPRIETES
Rem
Rem
***
Rem
***
Rem
Rem METHODES PRIVEES
Rem
Rem
***
Rem
***
Rem
Rem METHODES PUBLIQUES
Rem
Rem
***
Public Sub Ecouter(FormRowSet As Variant)
On local Error Goto Error_Sub
_FormRowSet = FormRowSet
_ecoute1 = CreateUnoListener("TVFAPEcouteAvant_",
"com.sun.star.sdb.XRowSetApproveListener")
_FormRowSet.addRowSetApproveListener(_ecoute1)
_ecoute2 = CreateUnoListener("TVFAPEcouteApres_",
"com.sun.star.sdbc.XRowSetListener")
_FormRowSet.addRowSetListener(_ecoute2)
Exit_Sub:
On error resume next
Exit Sub
Error_Sub:
TraceError("ERROR", Err, Name, Erl)
Stop
End Sub
Public Sub ArreterEcoute()
_FormRowSet.removeRowSetApproveListener(_ecoute1)
_FormRowSet.removeRowSetListener(_ecoute2)
End Sub
' GESTIONNAIRES DES EVENEMENTS XROWSETAPPROVELISTENERE
---
Function TVFAPEcouteAvant_approveCursorMove(event As Object) As Boolean
MsgBox "TVFAPEcouteAvant_approveCursorMove"
TVFAPEcouteAvant_approveCursorMove = true
End Function
'---
Function TVFAPEcouteAvant_approveRowChange(event As Object) As Boolean
MsgBox "TVFAPEcouteAvant_approveRowChange"
TVFAPEcouteAvant_approveRowChange = true
End Function
'---
Function TVFAPEcouteAvant_approveRowSetChange(event As Object) As Boolean
MsgBox "TVFAPEcouteAvant_approveRowSet"
TVFAPEcouteAvant_approveRowSetChange = true
End Function
'---
Function TVFAPEcouteAvant_disposing(event As Object) As Boolean
MsgBox "TVFAPEcouteAvant_disposing"
TVFAPEcouteAvant_disposing = true
End Function
' GESTIONNAIRES DES EVENEMENTS XROWSETLISTENERE
---
Sub TVFAPEcouteApres_cursorMoved(event As Object)
MsgBox "TVFAPEcouteApres_cursorMoved"
End Sub
'---
Sub TVFAPEcouteApres_RowChanged(event As Object)
MsgBox "TVFAPEcouteApres_RowChanged"
End Sub
'---
Sub TVFAPEcouteApres_RowSetChanged(event As Object)
MsgBox "TVFAPEcouteApres_RowSetChanged"
End Sub
'---
Sub TVFAPEcouteApres_disposing(event As Object)
MsgBox "TVFAPEcouteApres_disposing"
End Sub
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
On 07/12/2018 04:12 AM, Brian Barker wrote: At 19:30 11/07/2018 -0400, Carl Winerich wrote:It's back to the same old question; ...Indeed so - and the answer is very much the same as on the previous two occasions you have asked this.In a calendar, how can I put the day's date (number only) and week number and day-number-of-the-year in one cell?To put multiple items in one cell, you must concatenate the individual values. You can do this using the CONCATENATE() function:=CONCATENATE(one;two;three)or probably more easily using theoperator:=onetwothree- whereone,two, andthreerepresent your items - references or formulae.Note that you will have to take care of spacing, so you may need something like=one" "two" "threeinstead.Note that concatenation - expressed either way - both requires and produces text items. If you pass it numeric values, these are converted to text automatically on the fly. If you want more control over exactly how a value is represented, you may want to do the conversion yourself, using the TEXT() function, which allows you to specify the format of the converted value. So if you wanted, say, all your dates to appear as two-digit numbers, so the first of the month was "01" instead of just "1", your formula might start=TEXT(one;"00")" "...Particularly in cell A5, B5...etc which are all cells that contain only the day number of the month. Here's a link to the Calc calendar template I'm trying to use-https://extensions.libreoffice.org/templates/calendar-creatorThe template is very useful and I don't want to interfere or mess up the calendar creation - which it does perfectly.You'll have to puzzle out how to derive formulae for the required two new values for yourself, unless the answers are buried somewhere in the existing template. The functions WEEKNUM(), WEEKNUMADD(), and DAYS() may help. You can read the existing formulae in your template by selecting relevant cells and looking in the Input Line for each, of course.I trust this helps.Brian BarkerBrian,I understand the WEEKNUM, and DAYS functions. Maybe the real problem is within the cell A5?Existing cell (for the month of February) has the following formula;=$G30-$H30+$D32+$E32+COLUMN(A5)That stuff points to various parts of the spreadsheet and returns the correct numeric day number of the month.When I try to concat it with, say, =DAYS then Err:510 appears in the cell. Any variation of the data produces a similar error.=$G30-$H30+$D32+$E32+COLUMN(A5)" "=DAYS(A5,$January.A5)+1I've successfully used concat or =text...etc as you suggested and placed the formula in other blank cells in an unused portion of the spreadsheet and have obtained the desired result. But for simplicity it would be best to have the formula and result in the correct cell.Any ideas?Thank you,Carl -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Calculate difference between two dates (with time)
Hi, just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18. 22:20, why then =TEXT((A2-A1);"d:h:m") returns 30:6:50 instead 0:6:50? Thanks, Kruno -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
At 19:30 11/07/2018 -0400, Carl Winerich wrote: It's back to the same old question; ... Indeed so - and the answer is very much the same as on the previous two occasions you have asked this. In a calendar, how can I put the day's date (number only) and week number and day-number-of-the-year in one cell? To put multiple items in one cell, you must concatenate the individual values. You can do this using the CONCATENATE() function: =CONCATENATE(;;) or probably more easily using the & operator: =&& - where , , and represent your items - references or formulae. Note that you will have to take care of spacing, so you may need something like =&" "&&" "& instead. Note that concatenation - expressed either way - both requires and produces text items. If you pass it numeric values, these are converted to text automatically on the fly. If you want more control over exactly how a value is represented, you may want to do the conversion yourself, using the TEXT() function, which allows you to specify the format of the converted value. So if you wanted, say, all your dates to appear as two-digit numbers, so the first of the month was "01" instead of just "1", your formula might start =TEXT(;"00")&" "& ... Particularly in cell A5, B5...etc which are all cells that contain only the day number of the month. Here's a link to the Calc calendar template I'm trying to use- https://extensions.libreoffice.org/templates/calendar-creator The template is very useful and I don't want to interfere or mess up the calendar creation - which it does perfectly. You'll have to puzzle out how to derive formulae for the required two new values for yourself, unless the answers are buried somewhere in the existing template. The functions WEEKNUM(), WEEKNUMADD(), and DAYS() may help. You can read the existing formulae in your template by selecting relevant cells and looking in the Input Line for each, of course. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
