Use a calc to figure sum in months using date fields but numeric r
 

When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in message
...
When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in message
...
When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"SusiesPassion" wrote in message
...
Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in message
...
When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how
many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

You're welcome. Thank you. Hopefully someone has tried this before and can
help... I'm not sure what I'm doing wrong. I do appreciate your time.

"RagDyer" wrote:

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"SusiesPassion" wrote in message
...
Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in message
...
When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how
many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

Do you have any other ideas?

~S

"RagDyer" wrote:

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"SusiesPassion" wrote in message
...
Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in message
...
When doing a pricing structure, I need to use a date to calculate and then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how
many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

I'm sorry.
I thought we were done.

I thought that you were just commenting on how you revised my suggestion to
meet your exact requirements.

Re-reading your answer, do I understand that you're looking to round to the
nearest half?

If so, try this:

=ROUND((B1-A1)/30.41667/0.5,0)*0.5
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"SusiesPassion" wrote in message
...
Do you have any other ideas?

~S

"RagDyer" wrote:

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"SusiesPassion" wrote in message
...
Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years
returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in
message
...
When doing a pricing structure, I need to use a date to calculate and
then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how
many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

Yes, that did help, thanks so much. I forgot to format my cells to accept a
percentage, ah, duh, so it was working all along....just had a brain fade
when it came to formatting. Thanks so much again!!!! Yippee!! Happy
SuperBowl Sunday... I'm going home to enjoy the game now....

"RagDyeR" wrote:

I'm sorry.
I thought we were done.

I thought that you were just commenting on how you revised my suggestion to
meet your exact requirements.

Re-reading your answer, do I understand that you're looking to round to the
nearest half?

If so, try this:

=ROUND((B1-A1)/30.41667/0.5,0)*0.5
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"SusiesPassion" wrote in message
...
Do you have any other ideas?

~S

"RagDyer" wrote:

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"SusiesPassion" wrote in message
...
Thank you... let me be a bit more specific.
=(A3-$A$1)/30.41667 and then
=ROUND(C14,0.55)

The reason I need something like this is because we have different
maintenance end dates for differeent purchases so I need a standardized
format that will calculate and round any date from multiple years
returning
the number of months in increments of .5 months. Hope this makes sense.

Thank you so much for your help!!



"RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"susiespassion" wrote in
message
...
When doing a pricing structure, I need to use a date to calculate and
then
round into a whole number or decimal , for ex; from 1/1/5 - 6/15/5 how
many
months = 6.5 The return is then used to calculate costs involved with
different scenarios for sales and clients.

Picking up on that theme, one could use the DAYS360 function and divide by 30
for a standardized way of handling the variation in month lengths.

On Fri, 4 Feb 2005 16:51:55 -0800, "RagDyer" wrote:

BTW,
Jan.1 to Jun.15 is only 5.5 months, isn't it?

One approach might be to calculate 30 day months, where,
A1 = start - 1/1/05
B1 = end - 6/15/05

Format C1 as General or Number, and enter:

=(B1-A1)/30