| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. |
|
#7
|
|||
|
|||
|
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. |
|
#8
|
|||
|
|||
|
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. |
|
#9
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return a date 6 months from a date in another cell | Excel Worksheet Functions | |||
| Autofilter on date fields should refelct the underlying data (rat. | Excel Discussion (Misc queries) | |||
| Formula to calucate # of months based on a speificed date entered | Excel Worksheet Functions | |||
| calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
| Comparing Date Fields | Excel Worksheet Functions | |||
Linear Mode
