Showing Month Value for a Number

  • Thread starter Thread starter srm
  • Start date Start date
S

srm

I'm using Excell for XP and need some help. I have a worksheet in which I
reflect total number of hours left on a project. Lets say 198. If you take
198 and divide it by 160 (average number of work hours in a month) you get
1.2 months. With today being September 10 and adding on 1.2 months, the
project would be completed in October (I'm not worried about the exact
date).

What I'm ty to figure out is if it possible to change the resulting value of
data to reflect the name of a month. Right now I say 1.2 months from today
is October which I do manually. It would be nice to somehow take 198 hours
(or 1.2 months) and add it to today to get a value of October.

thxs

srm
 
The way I'd do it, since "months" are slippery concepts, having
different lengths in different contexts (i.e. is 2.0 months from
12/31/2003 equal to 2/29/2004, which returns "February" or 3/2/2004,
which returns "March"?), is to add workdays. If A1 contains the
hours:

A1: 198
B1: =WORKDAY(TODAY(),A1/8)

You can format B1 with Format/Cells/Number/Custom mmmm to get the
result of October.

WORKDAY() is part of the Analysis Toolpak Add-in (Tools/Add-ins...
check the appropriate checkbox). You can also include holidays to
exclude, see Help for details.
 
Thank you very much. I'll give it a try.

J.E. McGimpsey said:
The way I'd do it, since "months" are slippery concepts, having
different lengths in different contexts (i.e. is 2.0 months from
12/31/2003 equal to 2/29/2004, which returns "February" or 3/2/2004,
which returns "March"?), is to add workdays. If A1 contains the
hours:

A1: 198
B1: =WORKDAY(TODAY(),A1/8)

You can format B1 with Format/Cells/Number/Custom mmmm to get the
result of October.

WORKDAY() is part of the Analysis Toolpak Add-in (Tools/Add-ins...
check the appropriate checkbox). You can also include holidays to
exclude, see Help for details.
Click to expand...
 
I'm using Excell for XP and need some help. I have a worksheet in which I
reflect total number of hours left on a project. Lets say 198. If you take
198 and divide it by 160 (average number of work hours in a month) you get
1.2 months. With today being September 10 and adding on 1.2 months, the
project would be completed in October (I'm not worried about the exact
date).

What I'm ty to figure out is if it possible to change the resulting value of
data to reflect the name of a month. Right now I say 1.2 months from today
is October which I do manually. It would be nice to somehow take 198 hours
(or 1.2 months) and add it to today to get a value of October.

thxs

srm
Click to expand...

I would use, as an additional factor, the average number of workdays in a
month. Let us say that number is 21.

Then, with the number of hours left in A1, use this formula:

=WORKDAY(TODAY(),A1/160*21)

Format the result with a date format.

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

There is a third argument to the WORKDAY function which can be a reference to a
list of holidays. You could add a list of holidays, depending on how much
accuracy you require. Workday omits Sat and Sun.


--ron
 
Back
Top