Excel 2007 Query

shaz77_2

Hi All,

I'm trying to perform a little function on Excel 07 and to be honest it's half right but it doesn't do exactly what I want and I was hoping someone on here could help?

Basically if goods are received by me prior to 13:00 on a working day, I need this to have these goods distributed on the same date, if they arrive after 13:00 I need them to be distributed the following working day.

At the moment I have a column on Excel to record the date and time stock arrives at, a column to advise the date and time it was distributed at and a column to advise if the service level has been met. The problem is I cannot get this cell to function properly and the problem lyes with the times rather than the dates - any help would be great.

Thanks in advance

lesalanos

what formatting have you got in the cell now?

maybe this would work if not already like this

dd/mm/yyyy hh:mm from custom

shaz77_2

At the moment I have the formula within the service level cell, the formula is despatch date/time - arrival date and time. Any number greater than 0 equates to service levels not being met. My problem however is that stock arriving after 13:00 on a particular day which is depatched on the following day is also being flagged, however this should not be as it's within the service level.

Owain_Moneysaver

Okay. I'm using Openoffice Calc, but I hope it's similar.



You can probably be neat and make one big formula to calculate whether the service level is met - or just hide the working-out columns.

Cols C and D - I use the =HOUR formula to get the hour value from the date and time in Cols. A and B.

Cols E and F - I use the =DAY formula to get the day value from the date and time Cols. A and B.

Col G - If Day In and Day Out are the same I set this to 1

Col H - If Day Out is <> Day In I set this to 1. You could refine this to check it is actually sent next day.

Col I - if the Hour In > 12 then next day is allowed, so set this to 1.

Col. J - if Col. G (sent same day) is true then standard is met OR if sent next day and allowed next day are both true then standard is met. I check for sent next day and allowed next day by adding them and checking for the value 2.

I've pasted the formula in displayable mode under each column, descending rows so they can display without wrapping.

It's not elegant, but the essence of your problem is extracting the DAY and HOUR from the date and time, then doing IF statements on them.

Owain_Moneysaver

I've simplified the formula down; it now requires only cell An and Bn for Date In and Date Out and displays a Pass or Fail. The code in the result cell is

=IF(((DAY(B5))=(DAY(A5))=1)OR((IF((DAY(B5))=(DAY(A5));0;1))+(IF((HOUR(A5))>12;1;0))=2);"Pass";"Fail")

In the above line it's in row 5, so

A5 = dayandtime in
B5 = dayandtime out
C5 =IF(((DAY(B5))=(DAY(A5))=1)OR((IF((DAY(B5))=(DAY(A5));0;1))+(IF((HOUR(A5))>12;1;0))=2);"Pass";"Fail")



I've added Conditional Formatting for the values of "Pass" and "Fail"

shaz77_2

Thanks that was a great help and I've a good idea where I want to go with this. I copied your code for use on a dummy spreadsheet but an error message kept displaying and the word "OR" in the formula bar was highlighted, any idea what is wrong with this aspect of the code?

Owain_Moneysaver

A google suggests that Excel handles OR differently from Openoffice.

I don't have Excel so can't check but for Openoffice
IF((testa)OR(TESTB);true;false)
I think Excel wants
IF(OR(testa,testb),true,false)

Also Excel seems to use commas where OO uses semicolons. See here and here

so if you try doing it the long way as I did first, in J where I have

=IF((G5=1)OR(H5+I5=2);1;0)
I think Excel wants
=IF(OR(G5=1,H5+I5=2),1,0)

Owain_Moneysaver

Incidentally, checking for actual next-day despatch in Col H is awkward if despatch day is the first of the month (DAY=1) and so the previous day was the last of the month (DAY=31).

Have just checked and Excel stores dates in days, so adding 1 to a date increments it by 1 day, and rolls over if necessary, eg 31 Jan 11:42 + 1 = 1 Feb 11:42. So for sameday despatch you can check
=IF(DAY(dayin)=DAY(dayout),"sameday","notsameday")
and for nextday despatch you can check
=if(DAY(dayin+1)=DAY(dayout),"nextday","notnextday")
and it will handle the month rollover. What that simple check may not pick up is if dayin is 10 March and dayout is 10 April - the DAY() result will be 10 for both dates. But if doesn't sound like you would have stock hanging around for a month!

rmg1

This should be close (considering it's very early and I've not had a coffee yet):-

=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(AND(HOUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")=TEXT(A2+1,"dd/mm/yyyy")),"Pass","Fail"))

::edit::

I've put the date/times in A2/B2 for In and Out respectively and this formula goes into C2.

shaz77_2

rmg1 wrote: »

This should be close (considering it's very early and I've not had a coffee yet):-

=IF(TEXT(A2,"dd/mm/yyyy")=TEXT(B2,"dd/mm/yyyy"),"Pass",IF(AND(HOUR(A2)>=13,TEXT(B2,"dd/mm/yyyy")=TEXT(A2+1,"dd/mm/yyyy")),"Pass","Fail"))

::edit::

I've put the date/times in A2/B2 for In and Out respectively and this formula goes into C2.

Thanks for that, this seems to do the trick for me, however is there anyway to allow for weekends? If a product arrives after 13:00 on Friday it isn't required to be distributed until Monday.

rmg1

you'd have to use something like the networkdays function.
Let me have another look at it and I'll get back to you.