Month

  • Thread starter Thread starter Boenerge
  • Start date Start date
B

Boenerge

I know I have asked a similar question.
However, is it possible to get a formula to look at a cell that contains the
month and then use the number of days from the previous month?
Thanks in anticipation
Jason
 
With the date in A1 try:

=DAY(A1-DAY(A1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
What do you mean by "a cell that contains the month"? Do you mean the number
of the month? The name of the month? A full date formatted to display the
month either by name or number? Something else?

Rick
 
Hi Rick,
I mean that a cell at the top of the page will contain the name of a month.
I then want the formula to recognise this and use the number of days from the
previous month to work out the answer (which is number of hours that wre
available to me in the previous month).
formula so far:
=SUM(D61*37.5)/7.5*?
D61 is a cell that contains the number of staff
Times by the hours they are contracted per week = 37.5
Divide by the hours a day they work
Times by the number of days in the previous month
Which will then given me the total number of hours that were available to me
as this fluctuates month by month.
Thanks in advance
Jason
 
When you say "number of days in the previous month", do you mean physical
days or workdays? If you mean physical days, then Don has given you a
workable answer. If you mean workdays (5-day workweek assumed), then the
following should give you that...

=NETWORKDAYS(EOMONTH(DATEVALUE("1-"&H2),-2)+1,DATEVALUE("1-"&H2)-1)

This formula requires the Analysis ToolPak add-in (click Tools/Add-Ins on
Excel's menu bar). You can also specify an optional 3rd argument to point to
a list of holidays also (see the help file for NETWORKDAYS).

Rick
 
No need for DATEVALUE...

=DAY(("1-"&H2)-1)

=EOMONTH("1-"&H2,-2)

I don't know how (if) this holds up with various regional date settings.
 
Good point.... that does 'simplify' the formula somewhat...

=NETWORKDAYS(EOMONTH("1-"&H2,-2)+1,("1-"&H2)-1)

Rick
 
Back
Top