[Solved] Formulas - Calculating to a working day

Discuss the spreadsheet application

[Solved] Formulas - Calculating to a working day

Postby tangleleg » Mon Dec 04, 2017 2:51 pm

I have a form that was created in Writer
I've sorted all the formulas to calculate the various costs but I'm having a problem with the dates. I'm trying to work out how to code various action dates:
For example:

Client arrives on a specific date then there are several action dates prior to that date. ie
5 days prior
4 weeks prior etc. etc.

This is fine unless one or other of these dates falls on a weekend;
Client arrives on Sunday 30.09.18
5 days prior is no problem on Tuesday 25.09.18
But 4 weeks prior would be Sunday 02.09.18. As this falls on a weekend I need the date to move to the previous working day being Friday 31.08.18

Please can anyone give me any advise or tell me where I can read up on this. It would be massively appreciated.
Thank you in advance.
Last edited by Hagar Delest on Sun Dec 10, 2017 11:02 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 on Windows 7 Professional
tangleleg
 
Posts: 2
Joined: Thu Sep 14, 2017 10:43 am

Re: Formulas - Calculating to a working day

Postby acknak » Mon Dec 04, 2017 3:33 pm

Greetings and welcome to the community forum!

Sorry, Writer just doesn't support enough math to do this. You can do "five days prior" by simply subtracting 5 from a date, but calculating the day of the week and including logic (if/then/else) aren't supported.

One possible workaround would be to do all the calculations in OO Calc and then use the results in the Writer document, either through DDE links or by database merge to fields.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22686
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Formulas - Calculating to a working day

Postby tangleleg » Mon Dec 04, 2017 4:13 pm

Hi acknak
Thank you for responding so quickly.
I guessed it would include a few if's, then's etc but at least I know now and can stop driving myself crazy trying.
Thank you also for the alternative suggestion, I'll look into that.
All the best.
OpenOffice 4.1.2 on Windows 7 Professional
tangleleg
 
Posts: 2
Joined: Thu Sep 14, 2017 10:43 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 31 guests