Suppose I have a series of dates and I want to find the date of the first working day in the month before. In the list of dates the year changes along the list.
Thank you for your help.
Ubuntu 18.04
LO 6.0.7.3
What is the definition of first working day according to your cultural convention, job contract, shift organization (or whatever else could determine such definition) or is it just the first date for a specific month appearing in your data?
What do you mean by month before? Before which date, before today?
Working days are Monday through Friday except holidays, but I am comfortable forgetting holidays.
Take for instance the date 15Apr2017. The month before is Mar2017 and the first working day of Mar2017 is 01Mar2017 which is Wednesday. Another example, take 23May2018, the month before is Apr2018 and the first working day of Apr2018 is 02Apr2018, which is Monday. Yet another example, take 10Jan2019. The month before is Dec2018 and is first working day is 03Dec2018, a Monday. Hope this is makes my question clearer.
Thanks for the clarification – so month before means month before the date given
Hello,
aussuming your dates are in column A and start in row 2 then you may use:
=IFS(WEEKDAY(DATE(YEAR(A2);MONTH(A2)-1;1);2)<6;DATE(YEAR(A2);MONTH(A2)-1;1);WEEKDAY(DATE(YEAR(A2);MONTH(A2)-1;1);2)=6;DATE(YEAR(A2);MONTH(A2)-1;3);WEEKDAY(DATE(YEAR(A2);MONTH(A2)-1;1);2)=7;DATE(YEAR(A2);MONTH(A2)-1;2))
See example file and breakdown: FirstWorkingDayMonthBefore.ods
Note
- This only works, if your dates are real calc dates (i.e. are integers) and are not text.
- No holidays considered
Tested using LibreOffice:
Version: 6.4.0.3, Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Thank you for for your help. It solved my case.
Best regards.
There is no need to markup “Solved” in any way. A green check mark () next to the answer ist the indicator of a solved / correctly answered question.