Days past todays date

djaldente

New Member
Joined
Mar 21, 2017
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I make 2 payruns per month, one on the 15th and one on the 30th.

I have the date the invoice was received in one column.

I would like to make a days overdue column based on todays date and the date of the corresponding payrun.

For example an invoice received on 12th March would be due 15th April & an invoice received on the 17th March would be due 30th April. So if todays date was 5th May the invoice would be 5 days overdue.

Thanks in advance
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,570
Office Version
  1. 365
Platform
  1. MacOS
does this work for you
=IF(DAY(A2)<=15,TODAY()-DATE(YEAR(A2),MONTH(A2),DAY(15)),TODAY()-DATE(YEAR(A2),MONTH(A2),DAY(30)))
BUT invoices on the 31st of a month , I assume would be calculated from the 15th - is that so
in which case we need to add an OR to the test and add an extra month so its 15th of the next month - is that the case ?
And for FEB whoch is 28/29th what happens then
 

djaldente

New Member
Joined
Mar 21, 2017
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
does this work for you
=IF(DAY(A2)<=15,TODAY()-DATE(YEAR(A2),MONTH(A2),DAY(15)),TODAY()-DATE(YEAR(A2),MONTH(A2),DAY(30)))
BUT invoices on the 31st of a month , I assume would be calculated from the 15th - is that so
in which case we need to add an OR to the test and add an extra month so its 15th of the next month - is that the case ?
And for FEB whoch is 28/29th what happens then
Thanks for getting back to me mate, I managed to figure it out
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,570
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,127,028
Messages
5,622,287
Members
415,890
Latest member
macak333

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