Value Error when calculating dates, IF Function & WORKDAY Function

Question

Hi - I'm getting a value error returned when trying to calculate dates in a formula and am looking for help.

I'm looking to calculate a series of procurement dates (shipping, manufacture etc.) which subtract duration days from previously calculated days. This depends on whether the materials are being purchased locally or internationally thus working with different holiday calendars (Tables 3&4 on another sheet).

The formula seems to work for the first column (AS) which gets the date from a VLOOKUP formula (in AV) but then returns an error in subsequent rows (AO,AK) when International is selected in column AH??

Thanks

excelonlineadvisor · Answer

Can you share a link to access the file ?

chris_mercer · Answer

Excelonlineadvisor&nbsp;Hi File is herehttps://1drv.ms/x/c/5b76311aac56837f/ESAs6Na0iHVIlNvCoJH3deUBNeZT8cnUphKZqHB3DcJgaA?e=BLDFaF&nbsp;Thanks

excelonlineadvisor · Answer

Pls check the formula at AO9Current Formula = =IF(AH9="International",WORKDAY(AS9-AN9,Table3),WORKDAY(AS9,-AN9,Table4))Correction :- Instead of WORKDAY(AS9-AN9,Table3) , it should be WORKDAY(AS9,AN9,Table3)

chris_mercer · Answer

Excelonlineadvisor&nbsp;Hi&nbsp; Thanks for the help - yes that clears the value error when I amend the formula to&nbsp;WORKDAY(AS9,AN9,Table3)... but that adds the number of days to the previous date whereas I need to subtract the dates back from the previous i.e. AO12 subtracts 5 working days from AS12 date to return the earlier date.Weirdly when I re-entered the original formula into the cells with the minus sign the value error cleared??&nbsp;Appreciate your help&nbsp;

Forum Discussion

Value Error when calculating dates, IF Function & WORKDAY Function

4 Replies

Resources