Day Count for ACT/365 fixed convention

Hi,

I would like to know is there a function or formula to do the day count between the beginning date 2020/12/31 and the list of end dates attached for the ACT/365 fixed convention?

End date
2023/01/31
2023/02/28
2023/03/31
2023/04/30
2023/05/31
2023/06/30
2023/07/31
2023/08/31
2023/09/30
2023/10/31
2023/11/30
2023/12/31
2024/01/31
2024/02/29
2024/03/31
2024/04/30
2024/05/31
2024/06/30
2024/07/31
2024/08/31
2024/09/30
2024/10/31
2024/11/30
2024/12/31
2025/01/31
2025/02/28
2025/03/31
2025/04/30
2025/05/31
2025/06/30
2025/07/31
2025/08/31
2025/09/30
2025/10/31
2025/11/30
2025/12/31
2026/01/31
2026/02/28
2026/03/31
2026/04/30
2026/05/31
2026/06/30
2026/07/31
2026/08/31
2026/09/30
2026/10/31
2026/11/30
2026/12/31

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

This may do what you want. Put a start date in A1 and the formula in A2 copied down:

=EOMONTH(EDATE(A1,1),0)

OK.

1. Attach a workbook showing what you want.
2. Explain ACT/365 - what is it and what does it mean?
3. Explain why the formula I offered does NOT provide what you showed above.

There has been no real explanation of what you want or an explanation of why you think the formula doesn't work - you haven't given us a lot to go on.

This perhaps???

=TEXT(EOMONTH(EDATE(A1,1),0),"yyyy/mm/dd")

But just guessing ...

Oh, I see you've post-edited your original post - the list of dates has changed, but still no explanation of what you want. That's a bit naughty, as it makes a nonsense of the formula I offered. Please don't do this in future!

No reply.

If you want a day count, then simply subtract the start date from the end date and set the cell formatting to either General or Number.

This perhaps?

=LOOKUP(2,1/A:A,A:A)-A1

Just wondering if we are there yet? Has the formula in post #6 provided what you are looking for?

OK - so as I asked before, can you provide a mock up of what you want? I still have no idea from your description what the solution is meant to look like or where you want it.

This will give the exact number of days between the start of a year and the end of it:

AliGW on MS365 Insider (Windows) 64 bit

	D	E	F	G
3	Start:	01/01/2022		365
4	End:	31/12/2022		=E4-E3+1

Sheet: Sheet1

	D	E	F	G
3	Start:	01/01/2024		366
4	End:	31/12/2024		=E4-E3+1

Sheet: Sheet1

the workbook attachment function does not work on my post. I am not sure why

That's why the instructions at the top tell you how to do it without using the paper clip icon (sadly, it hasn't worked for years).

Does what I have just posted get us any closer? I have had to look up ACT/365 just to try to help you.

Based on my offering in post #11, this would do the ACT/365 fixed calculation:

=G3/365

So, the whole formula:

=(end_date-start_date+1)/365

=(E4-E3+1)/365

And based on the range in Column A:

AliGW on MS365 Insider (Windows) 64 bit

	C	D	E
1	ACT/365 for range in Column A:	1.25	=(LOOKUP(2,1/A:A,A:A)-A1+1)/365

Sheet: Sheet1

Still guessing, but I hope I'm getting nearer to what you have in mind.

No reply and you have gone offline, so if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.