At 12:41 14/06/2021 +0200, Silvio Siefke wrote:
I have a table like:
Service a need 10 meters par day.
Service b need 1 meters par day.
Service c need 3 meters par day.
Service d need 5 meters par day.
This mean 11.04.2022 - 25 working days = Project Start Day. The date
is enddate, I need the start day based on service values of meters
and only working days. Is there a way?
I think you've been given all the answer already, but it may be
helpful to spell it out.
o I hope the date column contains genuine date values and not just
pieces of text that look like dates.
o You would be wise not to include the word "service" in all the
second column entries. If you do, you will inevitable misspell the
word at some point and possibly not notice, generating errors in your
calculations. Put "service" as a column heading and just the relevant
letters - a, b, c, or d - in the cells.
o If the third column always has 250, you don't need that column at
all. Either use the 250 value in your formulae or else, if it might
ever be changed, put it once somewhere in your spreadsheet and refer
to that single cell in your formulae.
o I'm guessing (you haven't exactly said) that the length of each
process is 250 divided by the "meters per day" for each service. That
works simply for services a, b, and d, but service c requires
eighty-three *and a third* days. You must decide whether, in your
unexplained context, you require that to be truncated to eighty-three
or expanded to eighty-four. Since your final answer is just a date,
it has to be one or the other. You will need to modify any formulae
to deal with that question.
o Let's suppose you have dates in column A and a, b, c, etc. in
column B. Construct a table elsewhere - let's say in columns M and N
- with a, b, c, and d in column M and 10, 1, 3, and 5 in
corresponding cells of column N. Then =VLOOKUP(Bn,M$1:N$4,2,0) would
retrieve the appropriate value for each line in your data from the
table. So your start date becomes =An-250/VLOOKUP(Bn,M$1:N$4,2,0) if
all days count, or =WORKDAY(An,-250/VLOOKUP(Bn,M$1:N$4,2,0)) if only
Mondays to Fridays count. You should wrap something around the
250/VLOOKUP(...) part to cope with the fractional part I identified
earlier. These formulae will produce numbers, so you will need to
format the result cells (column) as Date in order for the result to
show as meaningful dates.
PS: You are too late to start those service B processes!
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy