Date Formulas

  • Thread starter Thread starter ADE
  • Start date Start date
A

ADE

Hi

I hope that one of you very clever people can help me solve m
problem,please!!!

I need to create three date formulas that calculate the last workin
days date in various time periods(weekly,monthly,yearly)

Each day the date in the day cell as shown below is changed to th
current days date,based on this change i need to automaticall
calculate the relevant date for the last working days date for th
other three time periods(week,month,year)

I want to be able to enter any date i desire in the day cell and hav
the others recalculate.

I am aware of the Eomonth formula but this only calculates the las
calendar days date,not working day.




DAY 30/03/2004
WEEK 02/04/2004
MONTH 31/03/2004
YEAR 31/12/2004


Thanks for your help

Ad
 
Hi ADE!

Last working day in the year:
=WORKDAY(DATE(YEAR(A1)+1,1,1),-1,Holidays)
Last working day in the month:
=WORKDAY(EOMONTH(A1,0)+1,-1,Holidays)
Last working day in the week:
=WORKDAY(A1-WEEKDAY(A1-7)+7,-1,Holidays)

In all cases Holidays is the name of a range containing holidays.

For last in the year, I find the next 1-Jan and deduct 1 working day.
For last in the month, I find first of the next month and deduct 1
working day
For last in the week, I find the next Saturday and deduct 1 working
day

WORKDAY is an Analysis ToolPak function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top