Re: [libreoffice-users] Start Date

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: users+unsubscribe@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