Round Date to Nearest Workday

[nationwide]

Good day! I'm attempting to write a formula that rounds a given date it to the nearest workday. For example, If I have the dates January 23, 2010 (which is a Saturday) and January 24 ,2010 (which is a Sunday) I want the formula to return January 25, 2010 (Monday). However, if the date is January 22, 2010 (a Friday), I want the forumula to return that date since it's a workday.

Any help would be much appreciated!

 

[shemayisroel]

Hi & Welcome,


Try the below with the date in question being in A1, feel free to copy down

=IF(WEEKDAY(A1)=7,A1+2,IF(WEEKDAY(A1)=1,A1+1,A1))

 

[nationwide]

...this works perfectly...thanks so much!

 

[shemayisroel]

...this works perfectly...thanks so much!

Your welcome

 

[barry houdini]

You could also use WORKDAY function for this

=WORKDAY(A1-1,1)

 

[shemayisroel]

You could also use WORKDAY function for this

=WORKDAY(A1-1,1)

Thanks for this Barry - never knew this function existed.

 

[gico1972]

I am using the suggested formula but need to establish the closest working day to cell A1 minus 30 days. I have tried the following but its not working.
=IF(WEEKDAY(A1)-30=7,A1+2,IF(WEEKDAY(A1)-30=1,D4+1,A1-30))

Any help is much appreciated.

 

[barry houdini]

Hello gico1972,

Did you get an answer for this?

You can use an amended version of my suggestion with WORKDAY, i.e.

=WORKDAY(A1-31,1)

That will give you A1 minus 30 days .......unless that date happens to be a weekend in which case you'll get the following Monday.

You can also add a holiday range if required

 

[Front Desk]

While I adapted this equation to fit my needs I registered on this site just to say thank you!!!

Hi & Welcome,


Try the below with the date in question being in A1, feel free to copy down

=IF(WEEKDAY(A1)=7,A1+2,IF(WEEKDAY(A1)=1,A1+1,A1))

 

[marcellebuff]

Is there a way to round every Friday to Thursday on top of rounding every Sat & Sun to Monday?

Hi & Welcome,


Try the below with the date in question being in A1, feel free to copy down

=IF(WEEKDAY(A1)=7,A1+2,IF(WEEKDAY(A1)=1,A1+1,A1))