Excel Formula

Question

need formula to find days took between 5 different dates from cell A with cell C, D,E and F also if any of the date closer to the date of A then calculate the days.&nbsp;

mathetes · Answer

From the brief description you give, it's hard to do anything elegant. But a simple formula like this will calculate the number of days between cell A2 and B2 through F2 (just copy the formula from B2 into those other cells)
=ABS(Days(B2,$A$2))
You can then find the minimum number by formula using =MIN(B3:F3)

By the way, the ABS in that first formula just gives an absolute (always positive) number... so it is the number of days in either direction from A2...

If you need more help, please post a more complete description, or perhaps a sample of the spreadsheet you're working on.

rekha354 · Answer

I am looking for a complex calculationIf A _ Conf. Delivery dateB_ received at storeC received by clientD in transit by storesNow need to calculate days took to deliver.From A (if any of the B,C,D cell have value or if any of the cell is near to the date of A. Means Lowest date to be picked n calculate.

mathetes · Answer

Rekha354&nbsp; Could you give some examples with data from real life, along with a description of the calculation you would do if you were doing it "manually"&nbsp; --- it's clear that you know what you mean, but your description, frankly, is not as clear as a complete example would be. Preferably several examples, maybe with one of them being an "out-of-the-ordinary" situation.&nbsp;Words alone are just naming the variables, but not actually describing what you're asking to do with them, except that it's clear you want to know how to calculate numbers of days between various dates....but it's also clear that's not all that you want.

rekha354 · Answer

Good Day,Please see the real date witch highlighted comments for formulas.Hope this will give you a clear picture of my requirements.A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB ACBuyers Ship-Name User ID Ref. Bid closingDate Status date Order-type-Desc Status Supplier PO.Date Base Curr. Est.Amt.Bas Order conf.date Days for PO ACK Real del.date Company QuotesSent/Rec Type Pri. text Sent Ship Rec. storage Year Rec.ship Pay date Days for PO Acknowledge Open Requisition Delayed DeliveryHAD TUGS HAMD TU-19-5015 13-01-2019 13-03-2019 OTHERS ORDER COMPLETED TER SERVICES COMPANY 13-01-2019 QAR 12,065.52 13-01-2019 15-01-2019 30-01-2019 1 (1/1) P URGENT 24-01-2019 2019 28-01-2019 10-04-2019 calculate days from PO Date J to Order cof. Date(M), also need to check networking days &amp; if any holidays inbetween the days. from O I need to check (T,U,W) If any of the cell missing consider other cells, In this example T is lesser value ) so can take T and complet the calculation.AAD PILOTS AMA PB-19-5016 13-01-2019 02-04-2019 SPARE PARTS ORDER COMPLETED ASR MACHINERY 13-01-2019 QAR 2,167.50 13-01-2019 13-01-2019 30-01-2019 1 (7/4) P URGENT 14-01-2019 2019 16-01-2019 31-03-2019 calculate days from PO Date J to Order cof. Date(M), also need to check networking days &amp; if any holidays inbetween the days. from O I need to check (T,U,W) If any of the cell missing consider other cells, In this example T with no value &amp; U &amp; W with values, nearest value is U ) so can take U and complet the calculation.RDA 55 TUGS REDA SB-19-5228 22-10-2019 15-10-2019 OTHERS INQ SENT 15-10-2019 QAR 1 (1/0) I NORMAL 2019 we used to segrete each status Cell H will represent and will count delayed days From today last action will be caluated from Cell F ( if any holidays or weekends that will be consideredSED 55 TUGS SRED NB-19-5229 27-10-2019 OTHERS QUOTES RECEIVED 16-10-2019 QAR 1 (9/4) I NORMAL 2019 we used to segrete each status Cell H will represent and will count delayed days From today last action will be caluated from Cell F ( if any holidays or weekends that will be considered

mathetes · Answer

Rekha354&nbsp; I'm sorry, but when I asked for an example of how you would calculate it manually, I was expecting you to take what you wrote last time and (1) add examples of the relevant dates that might appear in each of those fields, and then (2) show the calculation as it would be performed manually. So, for example, you wrote:If A _ Conf. Delivery dateB_ received at storeC received by clientD in transit by storesGive a representative date in each of those fields (extracted perhaps from this last barrage of data), and then, importantly, give an illustration using that data of what you mean by"Now need to calculate days took to deliver.From A (if any of the B,C,D cell have value or if any of the cell is near to the date of A. Means Lowest date to be picked n calculate.In other words, it would help if you would demonstrate what you mean by those calculations, using the dates that are embedded in this last dense collection of lots of dates and lots of words that just serve to obscure things.&nbsp;What you have written is not clear, or at least it's not clear why it's hard. In my very first response to your very first posting, I showed how to calculate the number of days that have elapsed between Date 1 and Date 2. I don't yet see what you're asking that you're saying is complex, except that what you've provided in this last is such a barrage of lots of jargon and multiple dates that could be A, B, C or D from the point of view of an outsider.&nbsp;A very big part of writing a clear formula (in Excel or in any program) is first clearly articulating what you're trying to accomplish.&nbsp; Maybe it's my problem, not yours, but I still don't have a handle on exactly--an important word, "exactly"-- what your task is here. I know you do get it, but you've not clearly explained it, at least not to me.

Forum Discussion

Excel Formula

8 Replies

Resources