DATEDIF problems - please help!

SimonDale

New Member
Joined
Dec 5, 2009
Messages
6
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
I believe this answers part of your question.
Code:
=IF(H3=YEAR(NOW()),"OK",IF(DATE(YEAR(F3)+3,MONTH(F3)-3,DAY(F3))<NOW(),"DUE","NOT p DUE?))<>
 

SimonDale

New Member
Joined
Dec 5, 2009
Messages
6
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

New Member
Joined
Dec 5, 2009
Messages
6
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>
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>
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

New Member
Joined
Dec 5, 2009
Messages
6

ADVERTISEMENT

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

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
I see that my code line got munged also here is the corrected one:
Code:
=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>
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
The board sometimes interprets > or < as HTML tags, try leaving a space at the open end like this......

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

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top