Hello Everyone,
I’m new in using formula involving dates. Is anyone could help me get the right formula if i want to get the exact count of days between a specific date from the end of the month then divide it by 30 to get the pro-rated number of days.
example. October 4, 2022 is the date and I want to get how many days it has between Oct 4 to the end of the month and divide it by 30.
Hi Pamela,
Let's say the October 4 date sits in A1, then you can use the following formula to perform the calculation you want:
=(EOMONTH(A1,0)-A1)/30
EOMONTH(A1,0) returns the last date of the month (End Of Month) based on the date in A1. Change the 0 at the end to a 1 and you get next month's end date and so on.
Deduct the date in A1, and divide the result by 30. Format the cell as General.
Beware that you need to correct the day count if you want to include the first day. By default, Oct-31 minus Oct-4 returns 27 days (31-4). If you want it to include the 4th you need to add 1.
The formula would then be:
=(EOMONTH(A1,0)-A1+1)/30
Riny
