LinkBack URL
About LinkBacks
Hi
What the easiest formula to count numbers between two dates ( only days in specific year )
A1: FROM
A2: TO
A3: The required year
kindly explain the formula
Hi
What the easiest formula to count numbers between two dates ( only days in specific year )
A1: FROM
A2: TO
A3: The required year
kindly explain the formula
Last edited by Undo; 01-14-2022 at 11:49 AM.
There are instructions at the top of the page on how to post a sample sheet.Thanks
Uploaded...
This is what you need:
=MIN(A3,DATE(A4,12,31))-MAX(A2,DATE(A4,1,1))+1
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
or... using the ranges in your sample:
=MIN(B2,DATE(E2,12,31))-MAX(A2,DATE(E2,1,1))+1
Thank you, Thats working!
if anyone have shorter and easier formula kindly share it with us
What is difficult about that formula? And I don't think it can be shortened
It's not that difficult!!
MIN(B2,DATE(E2,12,31)) returns the 31/12 of the chosen year, or the value in B2... whichever is lower.
MAX(A2,DATE(E2,1,1)) returns the 1/1 of the chosen year, or the value in A1... whichever is higher.
The two are then substracted, one from the other and 1 is added to give the correct answer.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Yes, it can ;-)Originally Posted by Pepe Le Mokko
=MIN(B2,E2&"-12-31")-MAX(A2,E2&"-1-1")+1
Nice one Root. Are you a believer in boasting that "Mine's shorter than yours"??
(Will I get an infraction for that comment, I wonder??!!)
Boasting? - Definitely no.
Rather a believer that people learn neat Excel tricks by looking at what others are doing. At least, that's how I've been learning
Me, too - proper magpie, me.
LOL!!!(Will I get an infraction for that comment, I wonder??!!)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hahaha. To whom it may concern...
You steal from me, but I steal from everybody.
=SUBSTITUTE(^^^,"steal","learn")
Thank you for the rep points, Glenn!
You're welcome... it saved a total of (i recall) just 4 characters, but it was novel (to me, anyway).
To me, length is of a second importance (can you believe this???). The beauty is in reducing the number of function calls and reference calls.
Time to stop... otherwise, I'll have to give us both an infraction...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks