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
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