Dates difference

[chandrashekar]

Hi,

I need a date difference which should take care of week ends. Thanks!

A1= 25/07/2011
B1= 23/07/2011 'I need date of Previous working date
C1=26/06/2011 'I need date of Next working date

 

[chandrashekar]

Hi,

Sorry a small change.

I need a date difference which should take care of week ends. Thanks!

A1= 25/07/2011
B1= 23/07/2011 'I need date of Previous working date
C1=26/07/2011 'I need date of Next working date

 

[Misca]

For B1 try =WORKDAY(A1,-1)+IF(WEEKDAY(A1)=2,1,0)
For C1 try =WORKDAY(A1,1)-IF(WEEKDAY(A1)=6,2,0)

The first part gives you the previous / next workday from the given date but since it looks like the Saturdays should be counted as working days you need the second part to adjust the normal WORKDAY function.

If you're using Excel 2003 or earlier you're going to need the Analysis Toolpack installed on your computer to be able to use the WORKDAY function.

 

[chandrashekar]

Hi,

Thanks for the reply.
I am not getting output as desired I tried todays date i.e. 01/07/2011 for this formula should give output as 30/06/2011 which is Friday and 04/07/2011 which is Monday.

Thanks,

Chandra Shekar B

 

[Misca]

Should the Saturdays be counted as workdays or not?
Your original question (both of them) seemed to count Saturdays as working days (25/7/2011 is Monday and you wanted the formula to return 23/7/2011 for previous workday).
If not (=workdays are Mon-Fri) use only the first part of the function (= the WORKDAY -part).

Also make sure you have the Analysis Tookpack installed if you're using Excel 2003 or earlier.

Tested with my Excel 2003 and 2010 and both seem to work just fine.

I am sure you have made sure the date you have entered as starting day is a date and not text?

 

[chandrashekar]

Sat & Sundays are holidays based on this forumula should work. Thanks

 

[chandrashekar]

Thanks a lot I got it.