Apr 25 2018
04:25 AM
- last edited on
Jul 25 2018
11:49 AM
by
TechCommunityAP
Apr 25 2018
04:25 AM
- last edited on
Jul 25 2018
11:49 AM
by
TechCommunityAP
Hi All
I dont know if anyone can help!! a concept that sounds simple in my head is just not coming into fruition when im trying to do it in Excel.
I am trying to find a formula that will calculate the week number of a certain date that will fall between a start and date. So for example if my start date is 25/04/2018 and an end date of 20/07/2018 this will be 13 weeks (Monday to Friday) , but if i want to know what week number the 13th June would fall on is there formula that can do this?
So far i have managed to use a formula where i physically just need to look where the start date falls between a Week start and week end by using the roll back to the previous Monday from the start date but the snag is when I enter a date that falls on a Monday i do not want it to roll back to the previous monday as this Monday would count as week one ??
if i am going about this all wrong or if there's an easier way i would be grateful for some advice
Apr 25 2018 04:28 AM
Apr 25 2018 04:48 AM
Or
=ISOWEEKNUM(A1)
if you are in this system
Apr 25 2018 04:48 AM - edited Apr 25 2018 05:02 AM
Hi Jamil
thank your for your response, will this give me the week number which relates to the year i.e Jan to Dec?
I am looking for i.e Start Date 18/12/17 End Date 09/03/2018 but 20/02/2018 would be week 11 ?
Apr 25 2018 06:17 AM
SolutionHi Susan,
If you'd like to calculate number of full weeks from Start Date that could be
=INT((<your date> - <Start date>)/7)
Jul 18 2019 02:06 PM
I've same problem as Susan mentioned, Let's take July month and the Employee started working on Jul1 (monday) and his ending date is 6(saturday) i would get 1 as output. If start date is Jul1 (monday) and end date is July 10 (wednesday) then i should get output as 2.
Another question: irrespective of start date(i.e, july 1 - July 6) if end date falls before tue (i.e, july 9) i should get output as 1 and so on for the following days.
appreciate your help in finding the solution, thanks in advance.
Vaman21
@Sergei Baklan wrote:Hi Susan,
If you'd like to calculate number of full weeks from Start Date that could be
=INT((<your date> - <Start date>)/7)
Jul 18 2019 02:40 PM
Here is a complete guide to date math (Including the Weeknumber function) + a Free quick reference card to date Math
https://www.youtube.com/watch?v=qIZxeOq-QDk
Hope that helps
Nabil Mourad
Apr 25 2018 06:17 AM
SolutionHi Susan,
If you'd like to calculate number of full weeks from Start Date that could be
=INT((<your date> - <Start date>)/7)