Link to home
Start Free TrialLog in
Avatar of ssblue
ssblueFlag for United States of America

asked on

Determine next due date on an ongoing bases.

Excel - Need to determine next due date on an ongoing bases.

Please see attachment
NextDueDate_v1.xlsx
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Try this in C2 and copied down:

=IF((TODAY()-B2)/(A2/24)<1,B2+A2/24,B2+(A2/24)*ROUNDUP((TODAY()-B2)/(A2/24),0))

In sections:
(TODAY()-B2)/(A2/24)  calculates the number of intervals that have occurred between the start date and today

If the interval count is less than 1 then a whole interval period has not happened so Start Date + Interval

If the interval count is greater than 1 then at least one interval period has occurred and partway into the subsequent interval so Start Date + Interval * Interval Count rounded up to next integer.

To explain further for the second part, lets use example of interval of 50 hours.

If time passed between Start Date and Today is 25 hours the interval count will be less than 1 so result will be start date + 50 hours.

If time passed between Start Date and Today is 75 hours the interval count will be 1.5 which rounded up to an integer gives 2 so result will be Start Date + 100 hours (Interval * 2).

Hope that makes sense.
Avatar of ssblue

ASKER

I think I understand. So are you saying that the dates will automatically change???
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ssblue

ASKER

Thanks for all the help with this, very much appreciated!
Glad to be of help.