How to calculate available working hours until due date *excluding* weekends and holdidays in DQL

Here’s a big ask I think. I am not great with dataviewjs so I usually opt for dql, and I’ve made this monster of a FLATTEN variable called Time-avail for a dataview TABLE query I have in my daily note:

FLATTEN (date(string(date(today) + "T" + this.🕒Work-end)) - date(string(date(today) 
+ "T" + this.🕒Work-start))).hours*(date(string(dateformat(Due, "yyyy-MM-dd") 
+ "T" + "00:00")) - date(string(dateformat(date(tomorrow), "yyyy-MM-dd") 
+ "T" + "00:00"))).days + choice(any(Due > date(string(dateformat(Due, "yyyy-MM-dd") 
+ "T" + this.🕒Work-end)), Due < date(string(dateformat(Due, "yyyy-MM-dd") 
+ "T" + this.🕒Work-start))), 0, Due - date(string(dateformat(Due, "yyyy-MM-dd") 
+ "T" + this.🕒Work-start))).hours AS Time-avail

This calculates, the amount of work hours I have left to work on a task. The work hours are determined by inline dv fields (🕒Work-start:: 09:00, and 🕒Work-end:: 09:00). Since I format my 🕒Work-start, and 🕒Work-end like a fool stuck in my ways, I have to manipulate them a bit, but the high-level explanation of this mess goes like:

amount-of-work-hours-available-today-based-on-current-time + amount-of-work-hours-on-Due-day-based-on-Due-Time + amount-of-work-hours-for-the-days-between-based-on-daily-work-hours

The calculation assumes the future days will have the same work hours, and counts weekends (my problem).

In my calculation, it’s not in that order for whatever reason, but this is what it does. The problem is that it calculates the weekends too ― as if I’d work on Saturdays, which I don’t, so it’s calculating for time I actually won’t have.

What I’m trying to do

Just seeing the amount of days until a task is due isn’t good enough for me, so I am trying to calculate the amount of work hours I have left to work on a task in my work project.

I am hoping there is a dataview guru in here to help me modify my code to exclude weekends and maybe give advice on how I can structure assigning work days… should I just make another inline field like 📆Work-start:: Monday / 📆Work-end:: Friday or maybe a way to combine these with the 🕒Work-start, and 🕒Work-end fields? Even more demanding: Holidays… Maybe for that I can use a Days-off: yaml field in my work project note and have this dv table pull that field and include it in the list of days to ignore.

I’m really trying to avoid dataviewjs, but I think this is do-able without it.

Things I have tried

See above

I’m realizing I forgot a section of the code but it turned out to be wrong in any case.

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