how to substract only working days from a specific date?

Question

assuming that we have a date of 18/06/2015 for example, and we want to substruct 5 days from it, (Saturday and Sunday not included).

I have thought to elaborate an algorihm with DateAdd()but i wanna know wether there is a function that that enables to do so

thanks in advance

If you're using Office 2010 or higher, check out [WorksheetFunction.WorkDay](https://msdn.microsoft.com/en-us/library/office/ff196438.aspx). — Comintern, Jun 20 '15 at 18:36

score 0 · Accepted Answer · answered Jun 20 '15 at 18:38

0

Try the workday worksheet function:

WorksheetFunction.WorkDay("2015-06-18",-5)

answered Jun 20 '15 at 18:38

vacip

5,246
2
26
54

I tried to use this function but it has that run-time error: 1004 error ("impossible to learn WorkDay property of the WorksheetFunction class") – Benss Jun 20 '15 at 19:04
Are you sure you are using correct syntax? This works for me. [See a similar problem here](http://stackoverflow.com/questions/10635048/excel-error-1004-unable-to-get-property-of-worksheetfunction-class-appear) – vacip Jun 20 '15 at 19:11
Try using it with constant values, as seen in my answer, to see if it works at all. – vacip Jun 20 '15 at 19:13
@Benss the error you have is usually caused by improper date format in the cell which the function is referencing to. – omegastripes Jun 20 '15 at 21:08
if the user uses this file with Office 2006, this function it will work ? or it's work just for Office 2010 and higher ? – Benss Jun 20 '15 at 21:23
Yes, this function works with previous versions of Excel, from 2003 and on (maybe even before, I'm not sure). – vacip Jun 20 '15 at 23:07

score 0 · Answer 2 · answered Jun 20 '15 at 18:38

0

=WORKDAY(A1,5)

Assuming you have whatever original date in cell A1.

answered Jun 20 '15 at 18:38

frisco

1
1

Since Bens wants to subtract 5 days that would be `=WORKDAY(A1,-5)` which yields the date 11/06/2015 – Clif Jun 20 '15 at 19:54
@Clif but me I used the French format dd/mm/yyyy !! how I should do ? – Benss Jun 20 '15 at 21:26
My apologies, I had noticed the different format, but had assumed that workday would work with whatever date format the computer's system used. I do have a question, when you put 18/06/2015 in a cell and press `Ctrl`+1 is the number sample 42173? – Clif Jun 20 '15 at 21:56
The date format doesn't matter, as long as Excel recognises it as a date. – vacip Jun 20 '15 at 23:09
Remember: inside a formula (if you are using date constants), or in VBA, always use the ISO standard formatting. That is: yyyy-mm-dd. This will always be recognised, and will work on every machine, regardless to its locational settings. – vacip Jun 20 '15 at 23:11
Does this function not exist in excel VBA? Office 365. – Edward May 25 '21 at 13:17

score 0 · Answer 3 · answered Sep 28 '22 at 00:28

It worked:

Fecha_Inicio = (Extr_DiaAp & " - " & Extr_MesAp & " - " & Extr_YearAp)

'the last was just a Date that i extracted from other cells. Fecha_Comp = WorksheetFunction.WorkDay(Fecha_Inicio, 25) 'Fecha_Comp is a Date that starts from Fecha_Inicio 25 working days ahead.

MsgBox Fecha_Comp

how to substract only working days from a specific date?

3 Answers3