Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index

At 12:41 14/06/2021 +0200, Silvio Siefke wrote:
I have a table like:

11.04.2022;service a;250
12.04.2022;service a;250
13.04.2022;service a;250
14.04.2022;service b;250
15.04.2022;service b;250
12.05.2022;service b;250
13.05.2022;service c;250
14.05.2022;service c;250
17.05.2022;service d;250
18.05.2022;service d;250

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.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.