Dataview available days counter

Hi!
I’m trying to set up the available days left on a project counter.
I in fact found a partial solution to my question this thread

Things I have tried

As mentioned in the thread I have the note [[Project]] which includes the keyword dueDate:: 2022-08-31.
With query :

`=([[Project]].dueDate - date(today)).days`

I get result 77 (since today is 2022-06-14)

Which is as expected.

What I’m trying to do

I’m trying to keep track of how many available days I have until the due date of the Project. Meaning I know that from 2022-06-20 to 2022-06-24 I won’t be able to put any work into my project which effectively cuts the due date by 5 days.

But if I change my due date to: dueDateCut:: 2022-08-26 and do:

`=([[Project]].dueDateCut - date(today)).days`

But when I get to next week e.g. 2022-06-27 my dueDateCut should again be 2022-08-31.

What works:

I managed to put together a workaround:

In [[Project]] I’ve created keywords unavailableEvent1:: 5 for the number of unavailable days, and I’ve created a counter keyword

unavailableClounter:: `= [[Project]].unavailableEvent1 + [[Project]].unavailableEvent2 + ... `

which gives 5 so far. Then I manually set the dueDateCut:: 2022-08-26 and at the top of my daily note I have:

Available days: `=([[Project]].dueDateCut - date(today)).days`, total days `=([[Project]].dueDate - date(today)).days`

And the idea is to delete unavailableEvent1 after it happens and move the dueDateCut manually. On the one hand, the manual solution might be good to keep me aware. On the other I’m lazy…so… you know.

Any ideas for a more elegant solution / workaround? :slight_smile: Or is it too much for dataview and I need dataviewjs or something completely different?
Thanks!

PS: I think that this is one thing and implementation of weekend days is second. Potentially very useful for work-related stuff.

Another way to think about this: your project due dates don’t change, but of you can’t work on it this week then date(today) and date(today)+7 (or 5 or whatever) are the same in terms of time left to work on it. In other words, can you think about adjusting the number to the right of the - in your “available days” calculation instead of manually changing the due date? So you’ll be calculating
available = ((due - today) - unavailable)
, which I think you already have the dataview for all the pieces.

Other thoughts:

  • Are your unavailable times project-specific? It might be easier to update them from your query note if not.
  • Also you could potentially list the unavailable times as date ranges (maybe a 2-item list of [start, end] or some other easy to parse format) and use date/duration math to make sure they are after date(today) before including them in the availble days subtraction. But save this for after you’ve got a simpler version of the subtraction working!
  • You mentioned weekdays vs. weekends calculation - I agree that is separate from your question about unavailable days, let me think about that one and search the internet a little!

Hey @TomcaT is that really what you expected? If I saw that some date was 77 (or 70 or 7) days away I would assume that it was on the same day of the week as today. But 2022-08-31 is a Wednesday and 2022-06-14 is a Tuesday! Were your expectations different from mine?

(The reason for this is I believe that the subtraction is done on “years, months, days” so giving a result of “0yrs, 2months, 17days” and then trying to convert that to days. But how many days are in 2 months depends on which months we are talking about! See the documentation for luxon, the library dataview uses for date and duration, for lots more detail if interested. However, any of the solutions on that page will only work with JS not with plain dataview query language.)

EDIT: This actually relates to your question above about removing weekend days as well. I moved into dataviewjs to experiment with that and I needed a full codeblock so that I could check my work along the way rather than an inline statement. Putting the codeblock inside a spoiler tag since it’s long, click the triangle to display:

Counting only workdays, not weekends - note this code does not address your unavailable days question.

If you copy-paste the code block, you may see extra sets of 4 backticks around the whole thing. Delete those, they are for making this forum happy.

```dataviewjs
function checkValidDate(day) {
	if (!dv.date(day)) { 
		console.error("Input could not be made a date: " + day); 
		return false;
	}
	return true;
}

function getWorkdaysBetween(start, due) {
	if (!checkValidDate(start) || !checkValidDate(due)) return null; 
	if (start.valueOf() > due.valueOf()) return "overdue!!!";

	/* Workdays are weekdays 1-5 (Mon-Fri), 6 & 7 are weekend */
	let workdays = [null, true, true, true, true, true, false, false];	
	let workdaysLeft = 0;
	for (let d = start; !d.hasSame(due, 'day'); d = d.plus(Duration.fromObject({ days: 1 }))) {
		if(workdays[d.weekday]) 
			workdaysLeft += 1;
	}
	return workdaysLeft;
}

/* Example to test, you would do dv.date([[Project]].dueDate) */
const dueDate = dv.date("2022-08-31");
const today = dv.date("today").startOf('day');

const fullDuration = dueDate.diff(today, "days").days;
const workdaysLeft = getWorkdaysBetween(today, dueDate);


dv.table(["Start", "Due", "Total Days Left", "Work Days Left"],
		 [[today, dueDate, fullDuration, workdaysLeft]]);

```

EDIT 2: Here’s an inline JS version that seems to work, but it is not very pretty. Definitely look at the code block above to understand!
$= Interval.fromDateTimes(dv.date("today").startOf('day'), dv.date(dv.pages("Project").dueDate.first())).splitBy(Duration.fromObject({ days: 1 })).filter(i => i.start.weekday <= 5).length
I don’t think Interval is exposed in DQL, so this does require the Javascript, or some other creative solution!

…well this is partially true, but the result of due-today has type duration and dataview does not support duration - 5 or duration - 2022-06-31. I’m able to do ((due - today) - (end-start)) to match the datatypes.

I’m no programmer so those suggested parsing and checking functions are new to me. But I’ll look into it, it sounds fun.

Here you might be right but I could not figure out how to do this. I got stuck on that mismatch of the data types. Then I could probably go to some more advanced things like ranges as you suggest.

Yes. I do not care really about exact days. It is more about man-hours I’m able to put into the project in the long term view.

Oh wow! Thanks this looks promising!
As I mentioned, I’m no programmer but I have a feeling that adding another function that would subtract my unavailableCounter from the Work Days Left and add another column Work Days Available should not be so hard. The next step of course would be parsing a table/list with unavailable dates. For now, I added:

const unavailableDays = 5;
const availableDaysLeft = workdaysLeft - unavailableDays;

Which gives me what I want for now :slight_smile:

I’m gonna dive into dataviewjs and add a few more days to unavailableDays :smiley:

1 Like