logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Excel Date Comparison: Verify Start & Deadline Dates, Return Results Based on Today's Date

dyscan 15186 18
Best answers

How can I build an Excel formula that compares start and deadline dates, returns before/on time/late, and also handles missing start dates by comparing against today’s date?

Check whether the compared cell is empty before doing any date comparison, and use TODAY() as the current-date reference [#17641576][#17641611] The logic has to cover separate cases: date exists vs. date missing, and before deadline vs. on time vs. late [#17641576][#17641959] If you want all of that in one cell, split the problem into individual conditions or even separate helper columns, because the nested IF needs to be built from those smaller pieces [#17642284]
Generated by the language model.
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Report a violation of the law
Reply | New topic
  • #1 17641457
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    Hello,
    I have such a problem - I need to compare the dates, namely I want to verify that the start date is consistent with the scheduled date. I want him to return the results: before the deadline, on time, after the deadline, and in addition, if he does not indicate the start date of the task, he verifies whether or not the date is required in relation to today's date. While there is no problem with the first part of the task, the comparison with today's (set) date is a problem. Excel goes crazy when the column it compares to is empty ...

    Excel Date Comparison: Verify Start & Deadline Dates, Return Results Based on Today's Date

    = IF (E47> G47; "before due date"; IF (E47 = G47; "on time"; "late"))

    = IF (D48> F48; "before due date"; IF (D48 = F48; "on time"; IF (F48 = "-"; "ATTENTION !!!"; IF (D48 F51; "before due date"; IF (D51 = F51; "on time"; IF (D51
  • ADVERTISEMENT
  • #2 17641576
    kindlar
    Level 42  
    Posts: 7820
    Help: 912
    Rate: 1603
    Put an additional condition on whether the cell is empty, if it does not count.
  • #3 17641600
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    Thanks for the answer. What formula?
  • ADVERTISEMENT
  • #4 17641611
    Zbych034
    Level 39  
    Posts: 4636
    Help: 543
    Rate: 1399
    Date of today is = today ()
  • #5 17641616
    kindlar
    Level 42  
    Posts: 7820
    Help: 912
    Rate: 1603
    Get the file here.
  • #6 17641638
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    The file is attached.

    The comparative date is deliberately entered into the cell because it does not always need the current state.
    Attachments:
    • Zeszyt1.xlsx (10.47 KB) You must be logged in to download this attachment.
  • ADVERTISEMENT
  • #7 17641753
    kindlar
    Level 42  
    Posts: 7820
    Help: 912
    Rate: 1603
    Check on the cell I corrected.
    [movie: af298e3c4c] https://filmy.elektroda.pl/71_1545390456.mp4 [/ movie: af298e3c4c]
    Attachments:
    • Kopia Zeszyt1-1.xlsx (10.34 KB) You must be logged in to download this attachment.
  • #8 17641767
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    Your formula does not take into account whether the due date has been exceeded. My point is that it should also return this information within one cell.

    In summary, I am interested in:
    1. is it ahead of schedule
    2. is it on time
    3. is it late
    4. if no start date is given, whether or not a start is required due to a reference date (eg today).
  • #9 17641897
    kindlar
    Level 42  
    Posts: 7820
    Help: 912
    Rate: 1603
    Once again.
    Attachments:
    • Kopia Zeszyt1-1.xlsx (10.36 KB) You must be logged in to download this attachment.
  • ADVERTISEMENT
  • #10 17641939
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    However, I can see that it is not that simple.

    Unfortunately, still not what I mean. Now, if there is no due date, shows NOT GIVEN, the point was to verify whether it is before or after the date for example today.

    For early start, it shows "not required" and should "ahead of schedule".

    Perhaps it cannot be done within one formula and you have to break it down into 2 columns ...
  • #11 17641959
    kindlar
    Level 42  
    Posts: 7820
    Help: 912
    Rate: 1603
    Enter the conditions depending on the date and whether the date is given or not.
  • #12 17641986
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    I think I will give up and they will do it manually ...

    Until I don't believe it can't be done.
  • #13 17642119
    Zbych034
    Level 39  
    Posts: 4636
    Help: 543
    Rate: 1399
    It is possible, but you need more commitment, the matter is simple and you have already given the basics.
  • #14 17642179
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    I've been thinking about it for a long time.

    I love the "know-but-not-say" answers.
  • #15 17642284
    Zbych034
    Level 39  
    Posts: 4636
    Help: 543
    Rate: 1399
    And I think you expect a ready :D
    Decompose a function into individual elements (for one condition) and then play with a compound function. Help is in the program, you just have to want to use it. if you want a cash from me, make a transfer to any foundation at least PLN 50 (you choose which one) and I will socially do the job for you. I don't feel like it otherwise. It is your choice and do not assume that it cannot be done :D
  • #16 17642288
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    I don't want to help, don't help.

    Better spend this time with your family.
  • #17 17642295
    Zbych034
    Level 39  
    Posts: 4636
    Help: 543
    Rate: 1399
    Help not do it for someone
    Don't worry about my family (kids long gone)
    I finish this because I don't like people with a demanding attitude
  • #18 17642317
    dyscan
    Level 10  
    Posts: 22
    Rate: 4
    Nevertheless, I wish you all the best for Christmas and the whole new year.
  • #19 17642320
    Zbych034
    Level 39  
    Posts: 4636
    Help: 543
    Rate: 1399
    thank you, the same to you
Reply | New topic
Report a violation of the law

Topic summary

✨ The discussion revolves around a user's challenge in Excel regarding date comparisons for task management. The user seeks to verify if a start date aligns with a scheduled deadline, returning results such as "before due date," "on time," or "late." Additionally, the user wants to check if a start date is required based on today's date when no start date is provided. Various responses suggest adding conditions to handle empty cells and using the TODAY() function for current date comparisons. The user expresses frustration over the complexity of creating a single formula to achieve all desired outcomes, considering breaking it into multiple columns. Some participants offer assistance, while others suggest that the task is manageable with proper commitment and understanding of Excel functions.
Generated by the language model.

FAQ

TL;DR: Build a one-cell status formula with IF/IFS and optional TODAY(). Excel allows 64 nested IFs, and "You can nest up to 64 IF functions." This covers Ahead/On time/Late and Start required, using an optional reference date cell. [“IF function”]

Why it matters: It helps project trackers quickly flag missing starts or overdue tasks without manual checks, in one formula.

Quick-Facts

  • Excel stores dates as serials: 1 = Jan 1, 1900; 2,958,465 = Dec 31, 9999. [“Date systems in Excel”]
  • TODAY() returns the current date’s serial and recalculates on workbook recalculation or open. [“TODAY function”]
  • IF supports up to 64 nested conditions for complex status logic. [“IF function”]
  • ISBLANK detects empty cells; pair with IF to avoid comparing blanks. [“ISBLANK function”]
  • IFS evaluates conditions in order and returns the first TRUE match. [“IFS function”]

Quick Facts

  • Excel stores dates as serials: 1 = Jan 1, 1900; 2,958,465 = Dec 31, 9999. [“Date systems in Excel”]
  • TODAY() returns the current date’s serial and recalculates on workbook recalculation or open. [“TODAY function”]
  • IF supports up to 64 nested conditions for complex status logic. [“IF function”]
  • ISBLANK detects empty cells; pair with IF to avoid comparing blanks. [“ISBLANK function”]
  • IFS evaluates conditions in order and returns the first TRUE match. [“IFS function”]

Is a single formula possible for “Ahead/On time/Late” and “Start required”?

Yes. Example (Start=D2, Due=F2, Ref=G2): =IF(ISBLANK(F2),"Schedule missing",IF(ISBLANK(D2),IF(IF(ISBLANK(G2),TODAY(),G2)<F2,"Not required yet","Start required"),IF(D2<F2,"Ahead of schedule",IF(D2=F2,"On time","Late")))). It checks due date, then whether start is blank, then compares dates. Replace cell refs as needed. [“IF function”]

What formula pattern prevents errors when comparing to empty cells?

Guard comparisons with ISBLANK. Example: =IF(ISBLANK(F2),"",IF(ISBLANK(D2),"Start required",IF(D2>F2,"Late","On time"))). This skips logic until required inputs exist. Use ISBLANK on any field that can be empty. [“ISBLANK function”]

How do I compare to TODAY() only when a reference date cell is empty?

Create a fallback reference: =IF(ISBLANK(G2),TODAY(),G2). Use this in your IF tests. As Microsoft notes, "Returns the serial number of today's date." This avoids manual updates when no custom reference is set. [“TODAY function”]

Can I replace nested IFs with IFS for readability?

Yes, with Excel 2019/Microsoft 365. Example: =IFS(ISBLANK(F2),"Schedule missing",AND(ISBLANK(D2),IF(ISBLANK(G2),TODAY(),G2)<F2),"Not required yet",ISBLANK(D2),"Start required",D2<F2,"Ahead of schedule",D2=F2,"On time",TRUE,"Late"). IFS returns the first TRUE result, simplifying long nests. [“IFS function”]

How can I calculate days early or days late?

Days late using a unified reference: =MAX(0,(IF(ISBLANK(D2),IF(ISBLANK(G2),TODAY(),G2),D2))-F2). Days early when started: =IF(ISBLANK(D2),"",MAX(0,F2-D2)). Alternatively, use DATEDIF(start,end,"d") for whole days. [“DATEDIF function”]

What’s the simplest step-by-step to build the status?

  1. Pick cells: Start=D2, Due=F2, optional Reference=G2.
  2. Define reference: Ref := IF(ISBLANK(G2),TODAY(),G2).
  3. Paste final formula: =IF(ISBLANK(F2),"Schedule missing",IF(ISBLANK(D2),IF(Ref<F2,"Not required yet","Start required"),IF(D2<F2,"Ahead of schedule",IF(D2=F2,"On time","Late")))). Use absolute refs if needed. [“IF function”]

Why do date comparisons sometimes behave oddly?

Excel stores dates as integers. For example, 2,958,465 represents Dec 31, 9999. Empty cells can coerce to zero in arithmetic, which predates recognized dates. Edge-case: Excel’s 1900 system wrongly treats 1900 as a leap year. Guard with ISBLANK before comparing. [“Date systems in Excel”]

How can I color-code statuses automatically?

Use Conditional Formatting. Add rules like “Cell contains ‘Late’” → red fill, “On time” → green, “Ahead” → blue, “Start required” → orange. Apply to the status column range. Order rules if overlaps exist. [“Conditional formatting in Excel”]

Does TODAY() hurt performance on large sheets?

TODAY() is volatile. It recalculates on each workbook recalculation and on open. Use it once in a helper cell (e.g., H1), then reference $H$1 across rows to reduce repeated calls. [“TODAY function”]

How many conditions can I chain in one cell?

Excel supports up to 64 nested IFs. For many branches, prefer IFS for clarity or split logic into helper columns to improve maintainability. [“IF function”]

My dates are text. How do I make comparisons work?

Convert text to real dates with DATEVALUE. Example: =DATEVALUE(A2). Use the converted value in comparisons. For unambiguous entry, input using DATE(year,month,day) or ensure regional formats match your settings. [“DATEVALUE function”]

What if the due date is missing but I still need “Start required” logic?

Decide a fallback rule. Example: treat missing due as "today" or a policy date. Formula: =IF(ISBLANK(F2),IF(IF(ISBLANK(G2),TODAY(),G2)>=TODAY(),"Start required","Not required yet"),<your normal logic>). Document the fallback so users understand results. [“IF function”]
Generated by the language model.
ADVERTISEMENT