Increment in date value if time exceeds a particular value

[sindhuja]

Hi,

Any help on my request will be highly appreciated..
very very urgent pls...

I have an excel with received date and received time and also completed date and completed time.

My criteria i have to finish the task within the day we received the request plus a day (day plus 1) provide the request received prior to 12:00

if the request received is greater than 12:00 then the date should be incremented by 1.

For ex i received a request on 15-8 at 12:30 then the received date should be taken as 16-8 since the time exceeds 12:00 and the completed date and time should be within 18-8 before 12:30

if completed within day plus then it is met else it is miss. The status should be in status column.

I have attached the sample file for the reference.

Immediate assistance will be very helpful..

Thanks in advance..

Sindhuja

[Bob Phillips]

Try this formula

=IF((D3+E3)<(B3+C3+2+(C3>--"12:00:00")),"Met","Not Met")

[sindhuja]

:friends:

[sindhuja]

Hi,

One more query..
I should consider only the weekdays..

Tha above if statement includes week ends also..

Kindly assist.

-Sindhuja

[sindhuja]

Hi,

One more query..
I should consider only the weekdays..

Tha above if statement includes week ends also..
also if the time values are blank it should not show the status either met or not met.. instead it should be as "error"

Kindly assist.

-Sindhuja

[Bob Phillips]

I am sure we can. I presume you mean that the completed date is 2 working days within the date received, but in your example one of the date receiveds is a Sunday, so what should we do with that?

[sindhuja]

hi,

We receive inputs only on weekdays...
Since it is sample worksheet i have not checked for the days i have entered...

Inputs - only on week days...
Output - Days should exclude week ends..
Criteria is Day we receive + 1 day if time is before 12:00

Hope its clear now...

Thanks in advance..

-Sindhuja

[Bob Phillips]

Try this formula

=IF((D3+E3)<=WORKDAY(B3,2+(C3>--"12:00:00"))+C3,"Met","Not Met")

[sindhuja]

Thanks for the quick response Xld..

But one more query... this works if the time values are blank also..
is there a way to avoid this... if time is blank then it should display as "no time"

-sindhuja

[Bob Phillips]

Try

=IF(D3="","No time",IF((D3+E3)<=WORKDAY(B3,2+(C3>--"12:00:00"))+C3,"Met","Not Met"))

[sindhuja]

Hi,

I tried using the if statement.. its not showing the result..
Is that anything addins i need to include to obtain the result...

-Sindhuja

[Bob Phillips]

You need to be more specific.

[sindhuja]

Hi,

I have attached the spreadsheet, the status is not reflecting it is giving as "#NAME"

This happens after using the workdays in the IF statement.

Kindly assist..

-sindhuja

[Bob Phillips]

You have to install the Analysis Toolpak.

Goto Tools>Add-Ins and tick that addin.

[sindhuja]

One more additional request..

I have two more colums rejected date (column J) and resolution date(column K).

I want to consider these two columns also. Criteria is the same Day + 2.
If there is no completed date then there should be rejection date and the caluculation start again from the rejction date and the resolution date (same as before). if the resolutin date is within Day + 2 then it is met in status column and if not "not met".

Hope i made it clear.
It would be great if this can be done using macro as we have around 5000 rows and if condition in 5000 rows slows down the file (opening, closing, saving)

Kindly assist.

-Sindhuja

[sindhuja]

Any clue..

[sindhuja]

Kindly help me pls..