DATEDIF problems - please help!

[SimonDale]

Hi All,

I wonder if someone could help me with this problem please. I feel like I've been bashing my head against a brick all for hours!

I provide hosting accounts for people and I bill them annually for them.
I have a column in my Excel 2007 spreadsheet, column F, which lists the date that the hosting account was opened. 26/01/2007 for instance.

I then have another column which I want to say "Due" or "OK" depending on if I need to bill the customer or not. I want it to say due if the anniversary date is 3 months or less in the future, or if its overdue (ie the date is in the past) and OK otherwise. I can then drive conditional formatting off that new column.

In another column, H, I put the last year that an invoice was raised. If I've done the invoice for this year I put 2009 in H for example.

I've struggled and failed to find a way to correctly identify that a hosting account started in 26/01/07 will renew 26/01/10 and therefore is less then 3 months from today.

I thought I had found a way around by taking 26/01/07 and concatenating a new string as 26/01/10 and then using datedif with that date, however datedif just says "!NUM" when I try to use my concatenated date.
This is what I presently have: =IF(DATEDIF(DATE(IF(H3=YEAR(NOW()),1+YEAR(NOW()),YEAR(NOW())),MONTH(F3),DAY(F3)),NOW(),"m")<=3,"Due", "OK")

The problem seems to be with adding 1 to the current year. I can't work out how to do that a different way though?!

And if I could get that working the next thing would be to make it say "OK" if it has already been invoiced - IE if H3 is year(now()) then "OK"

Many thanks indeed for any help you can provide!

Simon

 

[pbornemeier]

I believe this answers part of your question.

=IF(H3=YEAR(NOW()),"OK",IF(DATE(YEAR(F3)+3,MONTH(F3)-3,DAY(F3))<NOW(),"DUE","NOT p DUE?))<>

 

[SimonDale]

Hi,

Thanks for your reply. I found a way of doing it in the end (after nearly two days of tinkering). I made one column which works out the next anniversary - EG 26/01/07's next anniversay is 26/01/10. It also checks column H to see if it is bi-annual billing or not and if it is its adds 2 years to make the anniversary date instead of 1:

<code>=IF(H3="N",IF(DATE(YEAR(NOW()),MONTH(F3),DAY(F3))<NOW(),DATE(1+YEAR(NOW()),MONTH(F3),DAY(F3)),DATE(YEAR(NOW()),MONTH(F3),DAY(F3))),IF(DATE(YEAR(NOW()),MONTH(F3),DAY(F3))<NOW(),DATE(2+YEAR(NOW()),MONTH(F3),DAY(F3)),DATE(YEAR(NOW()),MONTH(F3),DAY(F3))))</code>

And then I check if that anniversary date is within the next 3 months and if the last invoice date (column I) is after 90 days before the anniversary date (IE that includes 90 days before and x days after it is due), and column I isn't "FoC". If it hasn't been invoiced, and its not "FoC" it sets colum J to "Action" or else "Done". Conditional formatting then sets the rows based on "Action" or "Done".

Code for column J:
<code>=IF(I3="FoC","N/A",IF(AND(NOW()>G3-90,G3-90<I3),"Done",IF(NOW()<G3-90,"Done","Action")))</code>

I think I've spotted a flaw in writing this post...anything biannual will never be billed because it will always add 2 years to it. ****! Back to the drawing board for that part!!

All the best

Simon

 

[SimonDale]

Hi,

Thanks for your reply. I found a way of doing it in the end (after nearly two days of tinkering). I made one column which works out the next anniversary - EG 26/01/07's next anniversay is 26/01/10. It also checks column H to see if it is bi-annual billing or not and if it is its adds 2 years to make the anniversary date instead of 1:

<CODE>

=IF(H3="N",IF(DATE(YEAR(NOW()),MONTH(F3),DAY(F3))<NOW(),DATE(1+YEAR(NOW()),MONTH(F3),DAY(F3)),DATE(YEAR(NOW()),MONTH(F3),DAY(F3))),IF(DATE(YEAR(NOW()),MONTH(F3),DAY(F3))<NOW(),DATE(2+YEAR(NOW()),MONTH(F3),DAY(F3)),DATE(YEAR(NOW()),MONTH(F3),DAY(F3))))

And then I check if that anniversary date is within the next 3 months and if the last invoice date (column I) is after 90 days before the anniversary date (IE that includes 90 days before and x days after it is due), and column I isn't "FoC". If it hasn't been invoiced, and its not "FoC" it sets colum J to "Action" or else "Done". Conditional formatting then sets the rows based on "Action" or "Done".

Code for column J:
<CODE><I3),"DONE",IF(NOW()<G3-90,"DONE","ACTION")))< code>

=IF(I3="FoC","N/A",IF(AND(NOW()>G3-90,G3-90<I3),"Done",IF(NOW()<G3-90,"Done","Action")))

I think I've spotted a flaw in writing this post...anything biannual will never be billed because it will always add 2 years to it. ****! Back to the drawing board for that part!!

All the best

Simon
</CODE></CODE>

 

[SimonDale]

It won't let me post beyond a less than symbol or edit my existing posts. Sorry!

 

[pbornemeier]

I see that my code line got munged also here is the corrected one:

=IF(H3=YEAR(NOW()),"OK",IF(DATE(YEAR(F3)+3,MONTH(F3)-3,DAY(F3)) LT NOW(),"DUE","NOT DUE"))<now(),"due","not due="" ))="">

Change LT to the less than symbol<now(),"due","not due="" ))[="" code]=""><now(),"due","not due="" ))[="" code]=""></now(),"due","not></now(),"due","not></now(),"due","not>

 

[barry houdini]

The board sometimes interprets > or < as HTML tags, try leaving a space at the open end like this......

=(A1 >2)*(A1< 4)

 

[pbornemeier]

OK - Thanks for the info on leaving a space at the open end of the > and < symbol. I had figured that noparse should stop that, but I guess not.