Date formula question

  • Thread starter Thread starter Tension
  • Start date Start date
T

Tension

Okay, I'm not very good with Date formulas...I have a question on tryin
to achieve something with a date.

I would like to be able to compare the date in A1 with the date in B
and have C1 reflect if the delivery was on time. So, if B1 is on o
before the date in A1 then it's on time. If it is after, then it'
late. How would I go about doing this?

Any help is appreciated.

Thanks in advance
 
Okay, I decided I need to make this spreadsheet a bit more complicate
(for me anyways). So, I'm attaching it. I'll summarize what I'd like t
accomplish and if anyone can help me out with this I'd greatl
appreciate it. Sorry, I'm not to good with the advanced formulas.

Column G shows the dates I need everything shipped by. Column H are th
delivery dates. I may have up to 3 deliveries before everything goe
out, and sometimes only 1 or 2. So, L is my balance on hand...and whe
that hits "0" then there will be no more deliveries, and everything ha
been shipped. So, the last delivery date in column H is what I need t
be <= to what's in column G, but only once L hits "0". Hopefully
explained that well enough.

Second Part... I then need a formula to take all of the cells in colum
I that show "On Time" and give me a percent out of all the deliverie
listed that were "On Time" in Cell I45. This sheet is updated daily, s
sometimes it's full, and other times there are only a few lines full
I'm not sure if that will matter or not, but thought I would point tha
out.

Hopefully someone out there will be able to help me out with this
Sorry I'm still so new with these formulas.

Thanks

Attachment filename: backlog.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55146
 
I doubt many people will download files, if you post again with a thorough
description of what you want you might have more luck.
Excel forum is linked to the public excel newsgroups and it is considered
bad
etiquette to attach files in newsgroups. Although it is true that there is
no
physical attachment attached to the post most regulars are skeptic to open
file
attachments from strangers. AFAIK most regulars use newsreaders and not
the excel forum



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Congratulations, Peo -- you beat Frank to it!!
LOL

Bob Umlas

Peo Sjoblom said:
I doubt many people will download files, if you post again with a thorough
description of what you want you might have more luck.
Excel forum is linked to the public excel newsgroups and it is considered
bad
etiquette to attach files in newsgroups. Although it is true that there is
no
physical attachment attached to the post most regulars are skeptic to open
file
attachments from strangers. AFAIK most regulars use newsreaders and not
the excel forum



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
Click to expand...
 
Okay, sorry about that.

I'll try my best. But, remember I'm very new to this and not sure if m
explanation will make since.

I have a backlog of inventory and I am trying to track the deliverie
of each order and if the complete order was shipped by the due date.

So, in tracking my inventory I have various basic formulas already i
the spreadsheet that are figuring my total on hand inventory of eac
order, as the seperate deliveries and their quantities are entered.

(Again, I could have as little as 1 shipment for the order and as man
as 3)

Lets say that D3 represents my on hand and that B1,2,3 represent th
delivery(s), A3 represents the due date, then C3 shows "on time" o
"late".

I need a formula that once the on hand reaches "0" it will take eithe
cell B1,2 or 3 as the delivery date (which ever one contains the las
delivery) and show in C3 the status.

I'm just not sure how to get it to recognize that it needs to determin
which cell holds the final date.

Once that is figured out. I would like to total column C to show the
of deliveries that are listed in the sheet that were "on time".

Please if I wasn't clear enough I'd appreciate feedback. This is
learning experience and again...any help is appreciated. Thanks fo
your time
 
I can understand if I'm not clear enough, or whatnot. But, if anyone
here is unable to help me with this, would any of you happen to be able
to direct me somewhere where I may be able to find my answers? I'm not
opposed to finding the answer myself, I'm just not sure where to
start.

Thank you.
 
One way

=IF(D3=0,MAX(B1:B3),"")

will return the latest date in B1:B3 when D3 equals zero
--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Okay, thanks so much! I'm not sure if I did what I should have, bu
here's what I did and it works...

I just used that formula and put it in a hidden column, then used th
formula given to me on here 1st in the column that I wanted to sho
refering back to the hidden cell. So, it works!

Now, if anyone has any thoughts on my % formula, I'd sure appreciat
it.

Thanks a bunch
 
assume you want to calculate what percentage A1 is of B1 you use

=A1/B1

formatted as percentage or

=COUNTIF(I2:I40,"On Time")/I45

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Okay, I take that back...

I only tried it on one entry, but once I applied it to the whole pag
it shows "Late" when the inventory hasn't reached 0 yet, meaning we'r
not done delivering. Or, it says "On time" in the rows that don't ye
have entries. I would assume that there is an easy way to account fo
those circumstances, but I don't know what that would be
 
If B1:B3 holds numeric dates then max will return the date which is
the latest

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top