• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Next Deposit Date

R

Rami9

Member
Hello, I have excel sheet that cell A2 contains (CURRENT DATE), and in cell B2 (DEPOSIT DATE) which deposited every 30 days,
Is it possible to create a formula in C1 to count how many days left to deposit next salary, and applies on the months after without the need to update the Deposit date manually?
 

Attachments

  • Book1.xlsx
    9.4 KB · Views: 12
I am afraid no, the deposit date is fixed every 30 days, what I am looking for is a formula that counts how many days remains from the current date
 
Do you mean every 30 days as in:
78250

or at the end of each month, or monthly on the same day of the month?
If you really mean every 30 days then as long as today's date is after the first (or sample) deposit date in cell B2 then in C2:
Code: 
=30-MOD(A2-B2,30)
where you can replace A2 with TODAY(). You may want to tweak it depending on whether you want to see 0, 1 or 30 when today's date is exactly on a date that payment is due. It may try and format cell C2 as a date so format that cell as a number without decimal places.
 
Last edited:
p45cal said:
Do you mean every 30 days as in:
View attachment 78250

or at the end of each month, or monthly on the same day of the month?
If you really mean every 30 days then as long as today's date is after the first (or sample) deposit date in cell B2 then in C2:
Code: 
=30-MOD(A2-B2,30)
where you can replace A2 with TODAY(). You may want to tweak it depending on whether you want to see 0, 1 or 30 when today's date is exactly on a date that payment is due. It may try and format cell C2 as a date so format that cell as a number without decimal places.
Click to expand...

I do mean (Monthly on the same day of the month) it occurs at the 27th.
it's always after the current date
I tried this formula: =IF(A2=DAY(27);0;DATEDIF(A2;B2;"D"))
What I want is to have the deposit date updated automatically


I appreciate your help, all of you guys.
 

Attachments

  • D date.xlsx
    9.6 KB · Views: 9
Rami9 said:
Yes, that's it, you solved it.
Click to expand...
Really?!!
If today is 5th May, that formula on my spreadsheet gives the next payment date as 27th June; shouldn't it be 27th May?

Perhaps:
Code: 
=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>27),27)
?
 
Hoi p45,
If today is 5th May, that formula on my spreadsheet gives the next payment date as 27th June; shouldn't it be 27th May?
Click to expand...
According to his example in post #6 it is the 27 th of the next month but I can be wrong,it's confusing
 
p45cal said:
Really?!!
If today is 5th May, that formula on my spreadsheet gives the next payment date as 27th June; shouldn't it be 27th May?

Perhaps:
Code: 
=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>27),27)
?
Click to expand...
Yes, It should be 27th May,
The payment (Deposit) is every 27th of the month, what i wanted is counting the days prior the payment day starting from the current day.

sorry guys for any confusion.
 
You must log in or register to reply here.
Back
Top