| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Calculation from a Due Date
Many thanks Sebastienm for your suggestion. I'm afraid though, that I do not
have 2 dates. I have only 1 column with a date within which a project requires to be completed. That is, once Contracts are signed, there could have a period of 24 months, 18 months or even 6 months for completion. Therefore, each month I need to submit a report showing the number of months remaining for each contract. "sebastienm" wrote: Hi, To get the number of days, just substract the two dates and format the cell as number: Say the dates are in A3 and A2 In A4: = A3 - A1 Now, format A4 as regular number -- this is the number of days. So determining the number of days is easy. However to determine the number of Month/Day, not sure how to get there easily. -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Hi all, I have a project table with due date in cell A4. In cell A5, I require to have it calculate, the number of days/months remaining till the Due Date. I haven't been able to find the answer by Googling. There are solutions for calculations between 2 dates and so on. Your advise would be very much appreciated, as I require to prepared this by the end of this week. Many thanks in advance. Prabha |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Calculation from a Due Date
In that case, compare with Today ie function TODAY( ):
= A2-Today() For the number of months: =(YEAR(Today())-YEAR(A2)) * 12 + MONTH(Today())-MONTH(A2) -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Many thanks Sebastienm for your suggestion. I'm afraid though, that I do not have 2 dates. I have only 1 column with a date within which a project requires to be completed. That is, once Contracts are signed, there could have a period of 24 months, 18 months or even 6 months for completion. Therefore, each month I need to submit a report showing the number of months remaining for each contract. "sebastienm" wrote: Hi, To get the number of days, just substract the two dates and format the cell as number: Say the dates are in A3 and A2 In A4: = A3 - A1 Now, format A4 as regular number -- this is the number of days. So determining the number of days is easy. However to determine the number of Month/Day, not sure how to get there easily. -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Hi all, I have a project table with due date in cell A4. In cell A5, I require to have it calculate, the number of days/months remaining till the Due Date. I haven't been able to find the answer by Googling. There are solutions for calculations between 2 dates and so on. Your advise would be very much appreciated, as I require to prepared this by the end of this week. Many thanks in advance. Prabha |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Calculation from a Due Date
ooops i reversed the last function i believe:
For the number of months: =(YEAR(a2)-YEAR(Today())) * 12 + MONTH(a2)-MONTH(Today()) -- Regards, Sébastien <http://www.ondemandanalysis.com "sebastienm" wrote: In that case, compare with Today ie function TODAY( ): = A2-Today() For the number of months: =(YEAR(Today())-YEAR(A2)) * 12 + MONTH(Today())-MONTH(A2) -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Many thanks Sebastienm for your suggestion. I'm afraid though, that I do not have 2 dates. I have only 1 column with a date within which a project requires to be completed. That is, once Contracts are signed, there could have a period of 24 months, 18 months or even 6 months for completion. Therefore, each month I need to submit a report showing the number of months remaining for each contract. "sebastienm" wrote: Hi, To get the number of days, just substract the two dates and format the cell as number: Say the dates are in A3 and A2 In A4: = A3 - A1 Now, format A4 as regular number -- this is the number of days. So determining the number of days is easy. However to determine the number of Month/Day, not sure how to get there easily. -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Hi all, I have a project table with due date in cell A4. In cell A5, I require to have it calculate, the number of days/months remaining till the Due Date. I haven't been able to find the answer by Googling. There are solutions for calculations between 2 dates and so on. Your advise would be very much appreciated, as I require to prepared this by the end of this week. Many thanks in advance. Prabha |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Calculation from a Due Date
Many thanks for your help. That worked perfect (well, of course it would...)
Until next time :-) , when I return back for some more help Best wishes for Christmas and A Prosperous New Year "sebastienm" wrote: ooops i reversed the last function i believe: For the number of months: =(YEAR(a2)-YEAR(Today())) * 12 + MONTH(a2)-MONTH(Today()) -- Regards, Sébastien <http://www.ondemandanalysis.com "sebastienm" wrote: In that case, compare with Today ie function TODAY( ): = A2-Today() For the number of months: =(YEAR(Today())-YEAR(A2)) * 12 + MONTH(Today())-MONTH(A2) -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Many thanks Sebastienm for your suggestion. I'm afraid though, that I do not have 2 dates. I have only 1 column with a date within which a project requires to be completed. That is, once Contracts are signed, there could have a period of 24 months, 18 months or even 6 months for completion. Therefore, each month I need to submit a report showing the number of months remaining for each contract. "sebastienm" wrote: Hi, To get the number of days, just substract the two dates and format the cell as number: Say the dates are in A3 and A2 In A4: = A3 - A1 Now, format A4 as regular number -- this is the number of days. So determining the number of days is easy. However to determine the number of Month/Day, not sure how to get there easily. -- Regards, Sébastien <http://www.ondemandanalysis.com "Prabha" wrote: Hi all, I have a project table with due date in cell A4. In cell A5, I require to have it calculate, the number of days/months remaining till the Due Date. I haven't been able to find the answer by Googling. There are solutions for calculations between 2 dates and so on. Your advise would be very much appreciated, as I require to prepared this by the end of this week. Many thanks in advance. Prabha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A specific date used for calculation of a new date | Excel Discussion (Misc queries) | |||
| Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
| End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
| Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
| Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend? | Excel Programming | |||
Hybrid Mode
