Predict Date & Time
- Thread starter malbarki
- Start date
Assume the Start date/Time is in B2
SLA is in B4
You could use something like: =IF(RIGHT(B4,1)="W",B2+7*LEFT(B4,(LEN(B4)-1)),IF(RIGHT(B4,1)="D",B2+LEFT(B4,LEN(B4)-1),IF(RIGHT(B4,1)="H",B2+LEFT(B4,LEN(B4)-1)/24,IF(RIGHT(B4,1)="M",B2+LEFT(B4,LEN(B4)-1)/(60*24),1))))
This allows you top use Weeks, Days, Hrs and Minutes in the SLA
SLA is in B4
You could use something like: =IF(RIGHT(B4,1)="W",B2+7*LEFT(B4,(LEN(B4)-1)),IF(RIGHT(B4,1)="D",B2+LEFT(B4,LEN(B4)-1),IF(RIGHT(B4,1)="H",B2+LEFT(B4,LEN(B4)-1)/24,IF(RIGHT(B4,1)="M",B2+LEFT(B4,LEN(B4)-1)/(60*24),1))))
This allows you top use Weeks, Days, Hrs and Minutes in the SLA
Hi Everyone
I would like to add minutes and seconds in the same.
For example
Start Date and time in A1 = 03/11/2013 11:02:34 AM
Minute is to add= 69 minutes ( B1)
Calculate Value shall be in C1=03/11/2013 12:11:34 PM
Please provide the formula to add minutes and seconds also in a date.
I would like to add minutes and seconds in the same.
For example
Start Date and time in A1 = 03/11/2013 11:02:34 AM
Minute is to add= 69 minutes ( B1)
Calculate Value shall be in C1=03/11/2013 12:11:34 PM
Please provide the formula to add minutes and seconds also in a date.
Let me elaborate more , I want the formula to predict the date & time for an incident resolution due date excluding non working hours
Example:
Weekends = Friday and Saturday
Working hours = From 8:00 AM to 17:00 PM
Holiday = 10/11/2013
Start Date and Time = 07/11/2013 08:00:00 AM
SLA = 24 Hrs
Expected Result
Resolution due date = 12/11/2013 14:00:00 PM
Note: The attchment holds my data and BBD formula for your reference
Example:
Weekends = Friday and Saturday
Working hours = From 8:00 AM to 17:00 PM
Holiday = 10/11/2013
Start Date and Time = 07/11/2013 08:00:00 AM
SLA = 24 Hrs
Expected Result
Resolution due date = 12/11/2013 14:00:00 PM
Note: The attchment holds my data and BBD formula for your reference
Somendra Misra
Excel Ninja
Hi malbarki,
Please have a look of attached file. Is this the solution you required.
I had used below array formula to get it. Note: CTRL+SHIFT+ENTER is required after entering the formula in cell.
=INT($A2)+IF(($B2/9)=1,0,INT($B2/9))+SUMPRODUCT(IF((ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9))))=Holiday)+(WEEKDAY(ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9)))),2)=6),1,0))+SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9)))),2)=5,1,0))+SUMPRODUCT(IF(OR(MOD($A2,1)>(17/24),MOD($A2,1)<(0/24)),1,0))+IF(OR(MOD($A2,1)>(17/24),MOD($A2,1)<(8/24)),$G$2+MOD($B2,9)/24,IF(TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0)>$H$2,$G$2+TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0)-$H$2,TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+IF($B2=9,9/24,TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0))))
Just advise if any issue.
Regards!
Please have a look of attached file. Is this the solution you required.
I had used below array formula to get it. Note: CTRL+SHIFT+ENTER is required after entering the formula in cell.
=INT($A2)+IF(($B2/9)=1,0,INT($B2/9))+SUMPRODUCT(IF((ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9))))=Holiday)+(WEEKDAY(ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9)))),2)=6),1,0))+SUMPRODUCT(IF(WEEKDAY(ROW(INDIRECT(INT($A2)&":"&(INT($A2)+INT($B2/9)))),2)=5,1,0))+SUMPRODUCT(IF(OR(MOD($A2,1)>(17/24),MOD($A2,1)<(0/24)),1,0))+IF(OR(MOD($A2,1)>(17/24),MOD($A2,1)<(8/24)),$G$2+MOD($B2,9)/24,IF(TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0)>$H$2,$G$2+TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0)-$H$2,TIME(HOUR($A2),MINUTE($A2),SECOND($A2))+IF($B2=9,9/24,TIME(MOD($B2,9),MOD(MOD($B2,9),1)*60,0))))
Just advise if any issue.
Regards!
Greeting Somendra Misra
The results of some entries are incorrect please see attached.
By the way any reason why the start and end time are not embedded within the formula and instead you used a cell reference....It's not an issue I just want to understand
Somendra Misra
Excel Ninja
Hey malbarki,
I know the enteries that you pointed out, thats why I said the job would have been lot easy if you would have using 2010. If you evalute the formula you will notice that for each type of cases such as if the task start after 17:00 hrs or after 00:00 hrs and before 8:00 hrs they had been accounted by sumproduct or some +. Now the problem is with those enteries where a task is starting on a holiday or ending on a holiday. To consider those I/you may need to put a BIG If in this formula. I will give it a try tommorow meantime you can try it.
May be some experts persons out here help you out on this to reduce it to a smaller version.
Since your weekends are not Saturday & Sunday we cannot use WORKDAY function that would have cut all the SUMPRODUCT portions.
And no special purpose of start & end time refrence through a cell. Just wanted to make it more dynamic if somebody changes the time then no need to find those value in such big formula because inside formula they will not be typed as 17:00 or 8:00 but some thinglike 17/24 or 8/24, so it will be hard. That's it.
Regards!
I know the enteries that you pointed out, thats why I said the job would have been lot easy if you would have using 2010. If you evalute the formula you will notice that for each type of cases such as if the task start after 17:00 hrs or after 00:00 hrs and before 8:00 hrs they had been accounted by sumproduct or some +. Now the problem is with those enteries where a task is starting on a holiday or ending on a holiday. To consider those I/you may need to put a BIG If in this formula. I will give it a try tommorow meantime you can try it.
May be some experts persons out here help you out on this to reduce it to a smaller version.
Since your weekends are not Saturday & Sunday we cannot use WORKDAY function that would have cut all the SUMPRODUCT portions.
And no special purpose of start & end time refrence through a cell. Just wanted to make it more dynamic if somebody changes the time then no need to find those value in such big formula because inside formula they will not be typed as 17:00 or 8:00 but some thinglike 17/24 or 8/24, so it will be hard. That's it.
Regards!
Hello Somendra Misra
It's too complicated for me I don't think I can write such formula, I think it's time to fight for V2010
Somendra Misra
Excel Ninja
To tell you the truth I haven't tried it before but if you think it will resolve my issue and wont impact other formulas in the workbook than yes it's ok
I have a similar question only my date and time are in two seperate columns. I need to predict 4 hours plus and 8 hours plus from the date time open. Perhaps a drop down menu to select between "time4" or "time8". This needs to be during the hours of work from 7am till 3pm excluding weekends and holidays. Can anyone help? I have attached a file.
Thank you well in advance if it can be done.
Thank you well in advance if it can be done.