Week of the year function

Hi Honeycoders!

I'm looking to get the week of the year, e.g.

Excel: Weeknum()

Is there a way I can get it?

Been referencing this cheatsheet while I work on the app - Has been nothing but a God-send.

Hi @karbonphyber,

Yes, the Honeycode Functions page is a great reference! :honeybee:

For your ask, as of now Honeycode does not have a function that returns the week number of the year, however I’ve passed this along to our team as an improvement request.

An idea that may work for you would be to create a table of Weeks, which you define manually. The Timesheet template is a good reference for this, as it has a Work Week table where weeks are defined, and the admin screen in the Manager app also allows you to add or update weeks.

Or you could create an app that returns data based on a specified time range. Here’s a post that shows how to display a list of data from a particular time range. You can apply this to automations too, where you write new rows to a table with columns that store a defined start and end date.

Hope that gives you some ideas while we review the improvement request. Also feel free to share more about your specific app and goals if you’d like some building ideas. :slight_smile: :honey_pot:


Seeing your response jolted me to create this equation that will tell me the first Monday of the year.
Please see below.

// Find the first Monday in January of this year.
// Monday=2; Therefore 7+2=9.
// Adding 1 to the MOD remainder gives me the date

From this, I'll be able to know how many weeks have passed since week 1.

// Dividing the number of dates and rounding down the remainder gets the number of whole weeks since start date of the year.
=1+ROUNDDOWN(DATEDIF([Formula above specifying start date],TODAY(),"D")/7,0)
=> 1+ROUNDDOWN(DATEDIF([Formula above specifying start date],[Date to evaluate],"D")/7,0)

So this is the complete formula which gets me the WeekOfYear()
=1+ROUNDDOWN(DATEDIF(DATE(YEAR([Date to evaluate]),1,1+MOD(9-WEEKDAY(DATE(YEAR([Date to evaluate]),1,1)),7)),[Date to evaluate],"D")/7,0)

If [Date to evaluate] is 17/1/2021, it'll be Week 2.

I've only just written it minutes before responding to you, so if this helps any honeycoder out there, that'll be great, but don't ping me if it doesn't work for a really odd year. I've only tested this on 2017 to 2021.

This way I won't have to worry about updatng a table which stores the weeks! However, cross-year dates might turn out funky. shrugs Then it's gotta be Week 53! lol

This is how it's written in my application


@karbonphyber, nicely done and very creative!

It's a great work-around to share for other Honeycoders :star2:

Good note on your IFERROR() catch too. It works well, but if you're interested in another option, in the app you can have data validation where a user would not be able to submit a date of a different year. (The app could show a greyed out submit button if a date is not in the same year). This can prevent users from submitting data that returns an error. :slight_smile:

Thanks and thanks!

That validation exists in my app currently! A bunch of columns provide the logic that determine which bunch of buttons to display, e.g. Can Complete, Can Refund, Can Forfeit, Can ....

Honeycode is really great. I also shared HoneyCode with a friend and he was blown away. I'll bet he's building his application now!


Awesome! Glad you knew about data validation, and yes, that's a good way to set up the buttons in your app :slight_smile:

We're so happy you're enjoying Honeycode so far! :honey_pot:

Keep your feedback and requests coming, it's all helpful as we continue to improve the product :honeybee:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.