Date Range Formlua

[Tinamation]

I am making a spreadsheet to track required documents and their due dates. This one document is due on the 25th of each month. I need a formula that says if the document was last completed within the time frame of, say April 25, 2011 to May 24, 2011, the next document will be due on June 25, 2011. However, if it were completed prior to April 25, the next due date would be May 25. The date completed gets entered in column H (not sure if you need to know that...)

Please let me know if you need more information.

Thanks!

 

[Roli001]

Need more information like

Is the document a spreadsheet that controls the creation date of other documents?
Is the document to check when it was last saved and alert if within certain timeframe?
Is the document reliant on user inputting last completed date or does it need to check that itself?

etc, etc...

 

[Tinamation]

The document is an actual piece of paper and this spreadsheet I'm making is to track that each staff member completes this document by the 25th of each month. When i get the doc, i enter the completed date in column H and would like the formula in column I to calculate when it is next due (based on the criteria listed above). Does that answer your questions?

 

[Roli001]

Is this relevant to the current date or is the past due date a constant?

 

[hellebore08]

This should do the trick..

assuming H3 contains the date the document was completed..
enter this formula in any cell..

=IF(DAY(H3)<25,MONTH(H3)+1&"/"&"25"&"/"&YEAR(H3),MONTH(H3)+2&"/"&"25"&"/"&YEAR(H3))

cheers!

-e.rgabrieldoronila

 

[Tinamation]

Oh that totally works. Thanks for your help!!

 

[Roli001]

This should do the trick..

assuming H3 contains the date the document was completed..
enter this formula in any cell..

=IF(DAY(H3)<25,MONTH(H3)+1&"/"&"25"&"/"&YEAR(H3),MONTH(H3)+2&"/"&"25"&"/"&YEAR(H3))

cheers!

-e.rgabrieldoronila

Good formula. I would also add an if statement to avoid Dec/Jan transitions:

=IF(MONTH(H3)<>12,IF(DAY(H3)<25,MONTH(H3)+1&"/"&"25"&"/"&YEAR(H3),MONTH(H3)+2&"/"&"25"&"/"&YEAR(H3)),IF(DAY(H3)<25,1&"/"&"25"&"/"&YEAR(H3)+1,2&"/"&"25"&"/"&YEAR(H3)+1))

 

[Tinamation]

Good call, will do. Thanks again!

 

[hellebore08]

haha.. yeah!

that's extremely cool Roli..

@Tinamation - don't forget Roli's if case.. that's important..