Day of the week in a year

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
257
Platform
  1. Windows
I want to compute the value for the "day of the week in a year." For example, Today (7th August) is Friday number 32 out of 52 Fridays in 2020. How do I get this value using a UDF?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
UDF, formula or plain vba?

The basic principle is INT((today-n)/7)+1 where n refers to Jan 1st of the current year.

edit:- @gaz_chops, this is the original, the other one is the duplicate (posted later).
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
@jasonb75 Oops, my bad

My reply from the other thread -

If a formula is acceptable, you can try

=WEEKNUM(A1)

You can tweak as to when the year starts/week starts.
 

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
257
Platform
  1. Windows
I need a UDF to give me which DOW is it and what is the current count out of total counts for the year. Eg. there are 52 mondays, 52 tuesdays, but 53 wednesdays in 2020. So, what is want is, for a given "date," what day it is (Friday), x of y days (32nd Friday out of 52 Fridays in 2020).
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904

ADVERTISEMENT

=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,DATE(YEAR(TODAY()),1,1),1):INDEX(A:A,TODAY(),1)),"ddd")="sat"))

will return the number of Saturdays that have been between the start of the year and today.

=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A, DATE(YEAR(TODAY()),1,1) ,1):INDEX(A:A, DATE(YEAR(TODAY())+1,1,0) ,1)),"ddd")="sat"))
Will return the number of saturdays in the current year.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Or this for number of days to date entered -

=WEEKNUM(A1,10+WEEKDAY(A1))
 

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
257
Platform
  1. Windows

ADVERTISEMENT

@jasonb75; the formula meets the requirement.
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
282
Try this:

=NETWORKDAYS.INTL(DATE(YEAR(TODAY()),1,1),TODAY(),SUBSTITUTE(1111111,1,0,WEEKDAY(TODAY(),2)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top