Calculate # of days between dates

  • 1
  • Question
  • Updated 3 weeks ago
I would like to be able to auto-calculate the # of days that an invoice is either overdue or # of days until due based upon the current date but haven't found how to make it happen (piece of cake in Excel but elludes me in MindManager).  Is this into the macro territory?
Photo of Bill Curtis

Bill Curtis

  • 3 Posts
  • 1 Reply Like

Posted 1 month ago

  • 1
Photo of Nick Duffill

Nick Duffill, Champion

  • 574 Posts
  • 224 Reply Likes
SmartRules can compare task dates but not calculate their difference, Formulas cannot access task dates, but yes, a macro could do this.

You would need more code around it do to anything useful, but the calculation would be based on:

daysOverdue = DateDiff("d", mTopic.Task.DueDate, Now)

Photo of Alex Gooding

Alex Gooding, Champion

  • 1208 Posts
  • 311 Reply Likes
Almost certainly a macro is the best way to go, but the MAP add-in has some task-related extended properties, including Days to Task Due. 

This requires both a task due date and a start date and is automatically updated by the property. Unfortunately it doesn't work once the task is overdue.
Photo of Bill Curtis

Bill Curtis

  • 3 Posts
  • 1 Reply Like
Thanks Nick and Alex!  It looks like it's time for me to begin dabbling in macros. :)
Photo of Nick Duffill

Nick Duffill, Champion

  • 569 Posts
  • 219 Reply Likes
Happy to help if you can describe what you want to do with the number of days overdue. E.g. if the result is placed in a Topic Property then you can use many other MindManager features to process it further.
Photo of Bill Curtis

Bill Curtis

  • 3 Posts
  • 1 Reply Like
I have a project dashboard that I created and part of that tracks client invoices.  I have smartrules setup to see at a glance if the due date has been passed, but I'd like to add to it the ability to see how many days until due or past due.  The property is currently setup as a simple date field . . .
Photo of Alex Gooding

Alex Gooding, Champion

  • 1208 Posts
  • 311 Reply Likes
Topic properties to show days until due and days overdue would be the way to go, but it would great if there was a facility to turn Due Date, Start Date and the current date into numeric formats which could be pasted into topic properties so you could use these directly in formulas (and SmartRules).
Photo of Alex Gooding

Alex Gooding, Champion

  • 1208 Posts
  • 311 Reply Likes
How is the dabbling in macros going - I'd be very interested to see the outcomes.

In case it isn't going as well you would like or you don't want to go down that route, I was also thinking of alternatives especially for overdue invoices. You indicated that you were using SmartRules to indicate if the date has passed, but I was wondering if you had played around with the Due Date options to show not the actual number of days but at least a bit more detail. 

In case you haven't these could provide a bit of a workaround. While there aren't as may options you can apply to past dates as there are to future ones in SmartRules, you can come up with rules like due before this month, due this month before this week and due this week before today which could then be reflected in appropriately-coloured tags. Of course you can do something similar with future-dated invoices.
(Edited)