| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate fractions using dates
I would like to calculate the fractions of various grants that can be spent
this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
Use the MIN and MAX functions...
=MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
Thank you. That gets me started.
However, I don't know what the 2 hyphens (--)in the formula represent. I tried to follow your recommendation and came up with this but it is not correct-the answer is too large. Any suggestions? =DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m")) My apologies if this question is too detailed. I am not sure of the protocol here. "Rick Rothstein" wrote: Use the MIN and MAX functions... =MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
The two minus signs force Excel to convert the text representation of a date
into a real date. You could use the DATE function instead... =MAX(DATE(2008,6,30),A1) =MIN(DATE(2009,7,1),A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... Thank you. That gets me started. However, I don't know what the 2 hyphens (--)in the formula represent. I tried to follow your recommendation and came up with this but it is not correct-the answer is too large. Any suggestions? =DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m")) My apologies if this question is too detailed. I am not sure of the protocol here. "Rick Rothstein" wrote: Use the MIN and MAX functions... =MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
Rick,
I just realized the error in the formula I sent in the last posting - I should have reversed the MIN and the MAX. Now it appears to be correct. I would still be interested in knowing what the 2 hyphens mean. Thank you. "Rick Rothstein" wrote: Use the MIN and MAX functions... =MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
Thank you!
"Rick Rothstein" wrote: The two minus signs force Excel to convert the text representation of a date into a real date. You could use the DATE function instead... =MAX(DATE(2008,6,30),A1) =MIN(DATE(2009,7,1),A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... Thank you. That gets me started. However, I don't know what the 2 hyphens (--)in the formula represent. I tried to follow your recommendation and came up with this but it is not correct-the answer is too large. Any suggestions? =DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m")) My apologies if this question is too detailed. I am not sure of the protocol here. "Rick Rothstein" wrote: Use the MIN and MAX functions... =MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate fractions using dates
You are welcome. Interestingly enough, when used within the MIN and MAX
functions, the double unary (double minus signs) is not required on the text representations of the dates. -- Rick (MVP - Excel) "lemonscented" wrote in message ... Thank you! "Rick Rothstein" wrote: The two minus signs force Excel to convert the text representation of a date into a real date. You could use the DATE function instead... =MAX(DATE(2008,6,30),A1) =MIN(DATE(2009,7,1),A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... Thank you. That gets me started. However, I don't know what the 2 hyphens (--)in the formula represent. I tried to follow your recommendation and came up with this but it is not correct-the answer is too large. Any suggestions? =DATEDIF((MIN((D3),"6/30/2008")),(MAX((E3),"7/1/2009")),"m")/(DATEDIF(D3,E3,"m")) My apologies if this question is too detailed. I am not sure of the protocol here. "Rick Rothstein" wrote: Use the MIN and MAX functions... =MAX(--"6/30/2008",A1) =MIN(--"7/1/2009",A1) -- Rick (MVP - Excel) "lemonscented" wrote in message ... I would like to calculate the fractions of various grants that can be spent this fiscal year. Some of the projects start this year, some started before this year, other projects end this year, some projects continue after this year, and others both start and end this year. I have figured out how to calculate the total months in the grant using DATEDIF. However, I don't know how to calculate the number of months for this year. To calculate the months for this fiscal year, I could also use DATEDIF. The first date would be either June 30, 2008 or the project start date depending on which is later. Likewise, the second date would be July 1, 2009 or the project end date, whichever is earlier. How would the formula automatically select the correct dates? Do I seem to be on the right track? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to prevent Excel converting imported fractions into dates | Excel Discussion (Misc queries) | |||
| Fractions show as dates | Excel Discussion (Misc queries) | |||
| fractions turn into dates when pasting in excel 2003 | Excel Discussion (Misc queries) | |||
| How do I keep excel 2002 from changing fractions to dates? | Excel Discussion (Misc queries) | |||
| How do I import fractions without data being converted to dates? | Excel Discussion (Misc queries) | |||
Linear Mode
