work day of the month

[Jagadeeshbs]

I have a excel sheet with more than 100 rows

[pre]

Activities                  Workday   Date                       Time
Send out closing report      18th     Tuesday, June 18, 2013     17:00
Track changes after closing  19th     Wednesday, June 19, 2013   17:00

[/pre]
Currently I manually change all the dates based on work day of the month…is there any formula I can use to update this sheet automatically when I choose month by data validation …?


If the work day falls on Saturday or Sunday I need to use the next working day.


Please help me


Jagadeesh B S

 

[dev.sutradhar1987]

Hi,


You can use this formulae. It works on the basis of if the Day is Saturday it adds 2 days to it to get you to Monday and if it is Sunday then it adds up 1 days to get you to monday.


IF(MOD(WEEKDAY(Cell_Ref),7)=0,Cell_Ref+2,IF(MOD(WEEKDAY(Cell_Ref),7)=1,Cell_Ref+1,Cell_Ref))


Cell_Ref = The Cell where you get the Final Date after whatever you do.This Formulae will convert the final Date to next Monday if the date is on Saturday or Sunday.

 

[Jagadeeshbs]

I have a excel sheet with more than 100 rows


Activities Workday Date Time

Send out closing report 18th Tuesday, June 18, 2013 17:00

Track chnages after closing 19th Wednesday, June 19, 2013 17:00

Open new period -5 Monday, June 24, 2013 9:00


Currently I manually change all the dates based on work day of the month…is there any formula I can use to update this sheet automatically if i can update the month in one cell.

 

[NARAYANK991]

Hi Jagadeesh ,


I am not clear about your requirement ; you will manually enter the Activities , that much is clear ; what else will you manually enter , and where do you want the formulae to be put in i.e. at present , you are entering all the following fields :


Activities

Workday

Date

Time


When the formulae are put in the worksheet , which of the above fields will they cover ?


You mention a drop-down for the month ; does it mean you will enter the following data ?


Month and Workday


The formulae should convert the above two items of data into a valid date ; is this what you want ?


Can you upload your workbook ?


Narayan

 

[SirJB7]

Hi, Jagadeeshbs!


Assuming you only enter the nth working day of current month in column B, in column C try this:

=DIA.LAB(FECHA(AÑO(HOY());MES(HOY());0);B2) -----> in english: =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0),B2)

and copy down as required.


Regards!

 

[Luke M]

If you have a date in A2, next workday is:

=WORKDAY(A2-1,1)


If you have the month number in A2, first workday of month is:

=WORKDAY(DATE(YEAR(TODAY()),A2,0),1)