Excel Help with date adding formulas!

[DécioC]

Hey everyone,
Im new here and with a kind of complex problem.
Im working on a sheet to manage a work production where depending on the duration of the task and the stating date of the task I wanna calculate when will the task end, and, to be able to set a new starting date for the task If the previous ends earlier or later. As i show in the attached file this is all working fine. But i have a problem which is, people shouldnt be working on sundays, so if a task takes 9 days to finish, in reality it should take 10 because one sunday would be included. Does anyone has any idea of a workaround? I came up with something but i ended up with cycle formulas :S

 

[Becky]

Welcome to the forum!

There's a formula called WORKDAY.INTL which might be what you're looking for. It calculates an date based on a start (or end) date and a number of days, but it does the calculation excluding weekends - and you can specify what is considered to be a non-workday. Here are the parameters:

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Start_date - Required. The start date, truncated to integer.

Days - Required. The number of workdays before or after the start_date. A positive value yields a future date; a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.

Weekend - Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur.

Weekend number values indicate the following weekend days:

1 or omitted - Saturday, Sunday
2 - Sunday, Monday
3 - Monday, Tuesday
4 - Tuesday, Wednesday
5 - Wednesday, Thursday
6 - Thursday, Friday
7 - Friday, Saturday
11 - Sunday only
12 - Monday only
13 - Tuesday only
14 - Wednesday only
15 - Thursday only
16 - Friday only
17 - Saturday only

Holidays - Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

Do you think this would do what you are looking for?

 

[DécioC]

Yes I'm aware of that formula and I tried it but ended with circular formulas for what i wanted :\ also, it counts the days and not the time itself so if something like this (15/10/2016 23:00 16/10/2016 05:00) happens it will count as 2 days and will mess the medium term results a lot! :s thanks anyway !

 

[DécioC]

Welcome to the forum!

There's a formula called WORKDAY.INTL which might be what you're looking for. It calculates an date based on a start (or end) date and a number of days, but it does the calculation excluding weekends - and you can specify what is considered to be a non-workday. Here are the parameters:

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Start_date - Required. The start date, truncated to integer.

Days - Required. The number of workdays before or after the start_date. A positive value yields a future date; a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.

Weekend - Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur.

Weekend number values indicate the following weekend days:

1 or omitted - Saturday, Sunday
2 - Sunday, Monday
3 - Monday, Tuesday
4 - Tuesday, Wednesday
5 - Wednesday, Thursday
6 - Thursday, Friday
7 - Friday, Saturday
11 - Sunday only
12 - Monday only
13 - Tuesday only
14 - Wednesday only
15 - Thursday only
16 - Friday only
17 - Saturday only

Holidays - Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.

Do you think this would do what you are looking for?

Replied under sorry

 

[Becky]

Yes I'm aware of that formula and I tried it but ended with circular formulas for what i wanted :\ also, it counts the days and not the time itself so if something like this (15/10/2016 23:00 16/10/2016 05:00) happens it will count as 2 days and will mess the medium term results a lot! :s thanks anyway !

Ah ok, shame about that because it's a nice formula!

You could count the number of Sundays within the date range and just add them to the end date. To avoid having a circular reference I would introduce a new column for the end date which excludes Sundays, and hide the other column (ie the date including Sundays) if necessary.

https://www.extendoffice.com/documents/excel/1421-excel-count-mondays-sundays-between-two-dates.html

 

[DécioC]

Ah ok, shame about that because it's a nice formula!

You could count the number of Sundays within the date range and just add them to the end date. To avoid having a circular reference I would introduce a new column for the end date which excludes Sundays, and hide the other column (ie the date including Sundays) if necessary.

https://www.extendoffice.com/documents/excel/1421-excel-count-mondays-sundays-between-two-dates.html

I managed to find a way to make it work using the solution you gave me! thanks a lot!

 

[Becky]

That's excellent news, I'm pleased to hear it!

 

[bootneck02]

Hi De'clor Welcome to PCR enjoy and join in.