relative time
 

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

assume your start date is in J7 and 3.41 is in J6

=DATE(YEAR(J7),MONTH(J7)+TRUNC(J6),DAY(J7))+DAY(DA TE(YEAR(J7),MONTH(J7)+TRUN
C(J6)+1,0))*(J6-TRUNC(J6))

--
Regards,
Tom Ogilvy


"jsc3489" wrote in message
...
Ok, say I have a number representing time. For instance, 3.41 months. I
would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the
3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them
all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

On Wed, 9 Nov 2005 07:12:08 -0800, "jsc3489"
wrote:

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?

If you just have the number 3.41, with no starting date, there is no way to
determine accurately the amount of time. This is because "month" is not
precisely defined -- it could be 28-31 days long.

On the other hand, if what you want to do is add 3.41 months to some date, then
you would have to first define precisely how you are calculating that number.

One way to visualize the problem is to consider adding 1-2 months to 29-31
January.

You could define the increment as being calendar months, where the integer
represents the number of calendar months, and the fractional part represents
the sum of the fractional part of the first and last months.

With that concept, 29 Jan 2005 + 3.41 months --
3 calendar months takes us to 30 Apr 2005
The 2 days in January = 2/31 = .064516 leaving
0.41- 2/31 * 31(days in May) = 10.71 so, depending on your convention,
either 10 May or 11 May.

You could also define it in terms of fractions of a year, so 3.41 months =
365.25/12*3.41 days = 103.7919 days.

So then 29 Jan 2005 + 3.41 months -- =DATE(2005,1,29+103.7919) -- 12 May 2005
or 13 May 2005 again depending on how you want to round fractional days.


--ron

relative time
 

".41 months" is a tricky concept. Is that .41 x 30 days? Or 31 days? Or
what if the month in which it applies is Feb - should I make it 28 days?
What if the period for which it applies begins in Feb but spans into March?
Do I use 28 days, 31 days, or prorate it based on the number of days that
fall in each month. So to avoid quibbling over these details I will use a
standard 30 day month in the formula. A1 would be the cell containing the
number of months:

=EOMONTH(TODAY(),INT(A1)-1)+DAY(TODAY())+(A1-INT(A1))*30
--
- K Dales


"jsc3489" wrote:

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

I have the following table to ease the computations:)

Date Days Sat,Sun Workable days Workable Hours
12/09/2005 30 5 21 168
4
01/09/2006 31 4 22 176
5
02/09/2006 31 4 23 184
4
03/09/2006 28 4 20 160
4
04/09/2006 31 5 21 168
5
05/09/2006 30 4 22 176
4
06/09/2006 31 4 23 184
4

--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!


"Ron Rosenfeld" wrote:

On Wed, 9 Nov 2005 07:12:08 -0800, "jsc3489"
wrote:

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?

If you just have the number 3.41, with no starting date, there is no way to
determine accurately the amount of time. This is because "month" is not
precisely defined -- it could be 28-31 days long.

On the other hand, if what you want to do is add 3.41 months to some date, then
you would have to first define precisely how you are calculating that number.

One way to visualize the problem is to consider adding 1-2 months to 29-31
January.

You could define the increment as being calendar months, where the integer
represents the number of calendar months, and the fractional part represents
the sum of the fractional part of the first and last months.

With that concept, 29 Jan 2005 + 3.41 months --
3 calendar months takes us to 30 Apr 2005
The 2 days in January = 2/31 = .064516 leaving
0.41- 2/31 * 31(days in May) = 10.71 so, depending on your convention,
either 10 May or 11 May.

You could also define it in terms of fractions of a year, so 3.41 months =
365.25/12*3.41 days = 103.7919 days.

So then 29 Jan 2005 + 3.41 months -- =DATE(2005,1,29+103.7919) -- 12 May 2005
or 13 May 2005 again depending on how you want to round fractional days.


--ron

relative time
 

"Ron Rosenfeld" wrote:

wrote:

I HAVE A REFERENCE DATE, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count BLAH BLAH BLAH


If you just have the number 3.41, with no starting date, there is no way to
determine accurately the amount of time. This is because "month" is not


WTF?

relative time
 

I have the following table to ease the computations. It is dynamic. :)

Date Days Sat,Sun Workable days Workable Hours
12/09/2005 30 5 21 168
4
01/09/2006 31 4 22 176
5
02/09/2006 31 4 23 184
4
03/09/2006 28 4 20 160
4
04/09/2006 31 5 21 168
5
05/09/2006 30 4 22 176
4
06/09/2006 31 4 23 184
4

--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!


"K Dales" wrote:

".41 months" is a tricky concept. Is that .41 x 30 days? Or 31 days? Or
what if the month in which it applies is Feb - should I make it 28 days?
What if the period for which it applies begins in Feb but spans into March?
Do I use 28 days, 31 days, or prorate it based on the number of days that
fall in each month. So to avoid quibbling over these details I will use a
standard 30 day month in the formula. A1 would be the cell containing the
number of months:

=EOMONTH(TODAY(),INT(A1)-1)+DAY(TODAY())+(A1-INT(A1))*30
--
- K Dales


"jsc3489" wrote:

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

On Wed, 9 Nov 2005 08:19:08 -0800, "jsc3489"
wrote:

"Ron Rosenfeld" wrote:

wrote:

I HAVE A REFERENCE DATE, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count BLAH BLAH BLAH


If you just have the number 3.41, with no starting date, there is no way to
determine accurately the amount of time. This is because "month" is not


WTF?

I don't know what thought you are trying to convey with your cryptic
abbreviation.
--ron

relative time
 

On Wed, 9 Nov 2005 08:11:04 -0800, "jsc3489"
wrote:

I have the following table to ease the computations:)

Date Days Sat,Sun Workable days Workable Hours
12/09/2005 30 5 21 168
4
01/09/2006 31 4 22 176
5
02/09/2006 31 4 23 184
4
03/09/2006 28 4 20 160
4
04/09/2006 31 5 21 168
5
05/09/2006 30 4 22 176
4
06/09/2006 31 4 23 184
4

How does this table relate to your original question of computing 3.41 months?

Perhaps you should start over and try to explain more clearly what it is you
are trying to do?


--ron

relative time
 

Oops, for total days:

=(DATE(YEAR(J7),MONTH(J7)+TRUNC(J6),DAY(J7))+DAY(D ATE(YEAR(J7),MONTH(J7)+TRU
NC(J6)+1,0))*(J6-TRUNC(J6))-J7)+1

and format the cell as General (it will probably default to date).

For a start date of 1/1/2005, this returned 103.3

for a start of 12/09/2005, this returned 103.71

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
assume your start date is in J7 and 3.41 is in J6


=DATE(YEAR(J7),MONTH(J7)+TRUNC(J6),DAY(J7))+DAY(DA TE(YEAR(J7),MONTH(J7)+TRUN
C(J6)+1,0))*(J6-TRUNC(J6))

--
Regards,
Tom Ogilvy


"jsc3489" wrote in message
...
Ok, say I have a number representing time. For instance, 3.41 months. I
would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the
3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them
all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

My formula could take into account the varying number of days within a month
even without the table, but you still run into problems when the .41 months
is split between two months.

Example:
..41 months would be 11.5 days approx. if I am going by February of next year
(.41*28=11.48). That is fine if I apply it from today: 3 months brings me to
2/9, and then the .41 brings me 11.5 days after that so that is 2/20/06.

Now if it were 11/17 today: 3 months makes it 2/24. So now I add .41
months. If I use the .41*28 I get the 11.5 days and I am on 3/7/05. But
part of that .41 month was in March, which has 31 days. If I used .41*31 I
would have 12.7 days, which puts me on 3/8, not 3/7.

So do I use 28 days to calculate the .41, do I use 31, or some average, or
what? There is no predefined way to handle this. It depends on why you need
to find that particular date and how accurately you need to find it and where
the 3.41 comes from. And in the end, it may just be a judgment call.

So I just used 30 days, period, as my "month" for the fractional portion of
the calculation. If you need to do it differently you will need to figure
out the answer to the above issues.

--
- K Dales


"jsc3489" wrote:

I have the following table to ease the computations. It is dynamic. :)

Date Days Sat,Sun Workable days Workable Hours
12/09/2005 30 5 21 168
4
01/09/2006 31 4 22 176
5
02/09/2006 31 4 23 184
4
03/09/2006 28 4 20 160
4
04/09/2006 31 5 21 168
5
05/09/2006 30 4 22 176
4
06/09/2006 31 4 23 184
4

--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!


"K Dales" wrote:

".41 months" is a tricky concept. Is that .41 x 30 days? Or 31 days? Or
what if the month in which it applies is Feb - should I make it 28 days?
What if the period for which it applies begins in Feb but spans into March?
Do I use 28 days, 31 days, or prorate it based on the number of days that
fall in each month. So to avoid quibbling over these details I will use a
standard 30 day month in the formula. A1 would be the cell containing the
number of months:

=EOMONTH(TODAY(),INT(A1)-1)+DAY(TODAY())+(A1-INT(A1))*30
--
- K Dales


"jsc3489" wrote:

Ok, say I have a number representing time. For instance, 3.41 months. I would
like to calculate (with some accuracy) to how many days this is.

I have a reference date, and I use =DAY(DATE(YEAR(B4),MONTH(B4)+1,0))
to count the days in my reference date. I'm looking to "pick apart" the 3.41
to change my formula to the next month, count the days, etc.

I could then multiply .41 to the days in the 4th month and then add them all
together to get my end result.

Trick is, to do any of the above. Any Takers?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!

relative time
 

you're funny Ron. I like you.

(0 \ | / 0) VW Bug's rule! It's a car with camel toe!

--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!