Comments on: Excel date functions with formula examples

This is the final part of our Excel Date Tutorial that offers an overview of all Excel date functions, explains their basic uses and provides lots of formula examples. Continue reading →

Comments page 2. Total comments: 595

  1. How does it convert to expiration dates? I am not seeing examples. I have current dates in cells but I need to know when it is 60 days to expiring.

  2. re: year function has hardly any problems.
    NOT TRUE! when I do as you say, the date is converted from something recent into some date in 1905. Doesn't work at all

    1. Hi! Please specify what you were trying to find, what formula you used, and what issue or error occurred. Include an example of the source data and the result you want to get.

  3. Hi - i have date and time in a cell. I wish to convert this to something like "Sun, Feb 4, 2024". What is the correct formula to do this? I am using =DateTime X - WEEKDAY(DateTime X,1)+1. This converts to any date/time to a Sunday of the week. When I try to sort in the Pivot table, these dates are sorted in Alphabetical order and not in the ascending/descending date order. Any ideas on what am I doing wrong? Any better formulas to convert any given datetime to the followng format "Sun, MMM DD, YYYY". Thanks.

  4. I'm wanting to reference a date in cell A1, in a formula, example CountIF=(H2:H50">=A1"). This way when I change the date in A1 the formulas will still work.

  5. Hello,

    I have a question, say if have a tracker that is tracking a running date from a column I have received an approval and I have another column where we have finalized the task. I want the running date to stop once there is an input in the date finalized column, but this wont affect other rows that has not finalized their tasks yet. 3 columns Date Form Approved, Age (Running days) and Date Task Finalized. This is for a Recruitment Tracker I am working on.

    Thank you!

  6. Hi,

    I am putting a date in A1 and in A2 I would like to add 7 days. this is easy enough, however I would like the cell to remain empty if the A1 date is not filled in, and the formula to stay and fill in automatically once the date is put in. So far I have not been successful with any if formulas and if I just use=A1+7 when there is no date it shows 07/01/00 instead of remaining empty. Can you please help? I have been trying to search for this for hours without success.
    Thank you

  7. I am trying to generate an aged creditors report in excel that calculates how many days overdue an invoice is based on a fix dated.

    DATEDIF function =DATEDIF("invoice due date", "fixed date", "d") works for all the invoices that are overdue and a number is returned however for all invoices that are not due yet i get the error #NUM!

    I know i can use the TODAY() function and it provides the necessary results, but i dont know how to lock that date in so that the returned figure doesn't change each day.

    Can you please advise

    1. Hi! I’m not sure if I got you right, since the description you provided is not entirely clear. If you want to remove the error message, try the IFERROR function. For example:

      =IFERROR(DATEDIF("invoice due date", "fixed date", "d"), DATEDIF("invoice due date", TODAY(), "d"))

      If it's not what you wanted, explain more accurately what result you wanted.

      1. Sorry if i wasn't clear.

        I either need help clearing the error message so that the formula works to give a negative number for the invoices that are not due

        Or

        I need to know how to use the TODAY function but have that has a fixed date as opposed to date that changes each day.

  8. Since the new year 2024 started, the following formula bring wrong number of "days". The year and month are correct.
    =DATEDIF(A2,TODAY(),"y") & " : " & DATEDIF(A2,TODAY(),"ym") & " : " & DATEDIF(A2,TODAY(),"MD")
    For eg., if we input the source date as 02-12-2014, the result is correct with 9:1:0. If we input 03-12-2014, then the result is wrong with 9:0:143.
    Please suggest correct forumula.

    1. I got the correct result from your data 03-12-2014. Check what you enter in the cell. I think it's New Year's fault.

      1. Also, I made one more mistake in my first query - the year "2014". It should be 2023. To be clear, please read as follows.

        The following formula brings wrong number of "days" in the scenario given below the formula. The year and month are correct.
        =DATEDIF(A2,TODAY(),"y") & " : " & DATEDIF(A2,TODAY(),"ym") & " : " & DATEDIF(A2,TODAY(),"MD")

        Scenario: If A2 date is 05-Dec-2023 when i check on 05-Jan-2024, the result is correct with 0:1:0. If I change A2 to 06-Dec-2023 (ie., one "day" more than the "day" i check), then the result is wrong with 0:0:143.

        So, when you are checking, please change the A2 date at least one day more from the current day you are checking on the formula. Eg., if you are checking on 06-Jan-2024, then the date in A2 should be 07-Dec-2023. The result comes 0:0:143. Please help.

      2. Sorry, I forgot to mention one thing that please check for one day "later date" input. For eg., if you are checking on 6-Jan, then put 7-Jan as input. I have posted my query on 2-Jan, and you have checked on 3-Jan. So, naturally, the answer will be correct with "0".

  9. I create a file daily and the header has the next business day's date (ie. Today is Thursday so the file I would be creating would be for Friday and if it's Friday, I'd need to make it for Monday.) Is there a formula I can use in my header that would automatically insert the next business day's date in the day, month, date, and year text format (Friday, November 17, 2023)? Thank you in advance.

  10. I'm struggling on setting a limit to getting data. You see we have graveyard shifters so if someone is scheduled at 9 PM, his shift ends at 6 AM the next day. What I'm trying to accomplish is - is there any way I can get all data only that's within that window?

    1. Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you FILTER formula. To create a condition when the data is between 9PM and 6AM the next day, try using these guidelines: Excel IF between two dates.
      I hope it’ll be helpful. If this does not help, explain the problem in detail.

  11. Something tells me that there is a simple formula for this, but I am unable to find anything in searching. I am looking to set up a validation to check the value of the adjacent column for a value and then offer a limited range based on that value. In this case, I have a column for Decades, the next column is for the Years. I want to be able to limit the selection of years based on the decade selected. For ex. if I choose "1980" for the decade, I want the adjacent cell in the next column to provide a validation list of the years: "1980, 1981, 1982, ... 1989" only. If the next row I select "2000", then I only want the years 2000 - 2009 listed in the validation.

    Is there a formula can I use to provide that in a validation?

    Thanks.

    1. Hi! To get a list of valid years based on the value in cell A2, use the formula:

      =A2+ROW(A1:A10)-1

      You can use this formula to data validation in cell B2.
      You can also create a drop-down list using this formula. Write this formula in cell D1, for example, and create a named range from cells D1:D10 with the name Decades. Then create a drop-down list with the named range Decades as the data source:

      =Decades

      Read more: Insert drop down list from a named range.

      1. THANK YOU! That worked great!

  12. To all Excel Experts,
    I am calculating the NEXT date of service based on the Maximum or next engine hour;

    Last Service Eng. Hr During Last Svc "Current Engine Hr (02-Nov) Engine Hr For NEXT Service "Date of NEXT Service
    2-Nov-23 16148 16148 16398 ???
    29-Oct-23 11279 11292 11529 ???

    Kindly show me a formula how to have the dates when Engine hour for Next Service change.

  13. To all the excel experts, I hope someone could assist or show me how to create formulas with multiple conditions.

    A B C D E F
    No. Date Report No. Reason Report returned? Y, N, NA FOLLOW UP NEEDED? DUE, OVERDUE, NOT DUE, NO ACTION NEEDED
    1 01/10/2023 23S16387 Second Opinion Y EXCEL FORMULA?
    2 05/10/2023 23S16767 Genomics N EXCEL FORMULA?
    3 09/10/2023 23S15993 Trial NA EXCEL FORMULA?
    4 25/10/2023 23S15300 MDT NA EXCEL FORMULA?

    Need to follow up second opinion and genomics only

    Y Means report returned and no need for follow up
    N report not returned and need follow up
    NA NOT APPLICABLE means no report needed

    EXCEL FORMULA
    IF Report returned=Y should indicate NO ACTION NEEDED
    IF Report returned=N and IF DATE is = or more than 14 days should indicate DUE
    IF Report returned=N and IF DATE is = or more than 30 days should indicate OVERDUE
    IF Report returned=N and IF DATE is less than 14 days should indicate NOT DUE
    IF Report returned=NA NO ACTION NEEDED

    I need help with if multiple formulas for follow up reports, to be able to check daily updates.
    I tried IF and and function but its also error its multiple conditions

  14. To all the excel experts, I hope someone could assist or show me how to create formulas with 2 or 3 conditions.

    I need help with if multiple formulas for follow up reports, to be able to check daily updates

    A B C D E F
    No. Date Report No. Reason Report returned? Y, N, NA FOLLOW UP NEEDED? DUE, OVERDUE, NOT DUE, NO ACTION NEEDED
    1 01/10/2023 23S16387 Second Opinion Y EXCEL FORMULA?
    2 05/10/2023 23S16767 Genomics N EXCEL FORMULA?
    3 09/10/2023 23S15993 Trial NA EXCEL FORMULA?
    4 25/10/2023 23S15300 MDT NA EXCEL FORMULA?

    Need to follow up second opinion and genomics only

    Y Means report returned and no need for follow up
    N report not returned and need follow up
    NA NOT APPLICABLE means no report needed

    EXCEL FORMULA
    IF E=Y NO ACTION NEEDED
    IF E=N and IF B is = or more than 14 days DUE
    IF E=N and IF B is = or more than 30 days OVERDUE
    IF E=N and IF B is less than 14 days NOT DUE
    IF E=NA NO ACTION NEEDED

  15. Date1 ; 05/10/2023 4:00:00 AM
    Date2 : 05/10/2023 6:30:00 AM

    Result : how many hour ?

    how to make formula ?

    =DATEDIF(Date1,Date2,"H")

  16. Dear Team,
    I have a list of dates over a 5 year period, and would like to be able to add a column to show what day each date was, so I can filter data by day.
    Apart from using filter, and chosing every 7 days for 5 years, is there any other way of doing this?

    Thank you for any help

  17. Aloha, I would like to create a formula that calculates the difference between dates that also stops calculating once action is completed.

    I am calculating the amount of time it takes to process transactions, but I don't know how make the date stop counting once the action is complete. Any assistance is greatly appreciated.

    1. Hello!
      To prevent your date from automatically changing, you can use several methods:
      1. Use Shortcuts to insert the current date and time
      2. Use the recommendations from this article in our blog.
      3. Replace the date and time returned by the TODAY function with their values. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V.

      1. Shabba beeling shoutout uptop snap

  18. Hello! I am trying to make a formula that will show a checkmark symbol or say "YES" if a project's completion year is within the last five years, without having to manually update the current year every year.

    I have tried a couple of different formulas that aren't working. They're showing me the checkmark or the "YES," but it is happening no matter what the year is in the previous column, even if the year is way past five years from today.

    A lot of the formulas I have found are specific to full dates (MM/DD/YYYY), and I only have years, so I need a formula that is specific to years only and not dates.

    The column with the years completed is Column G, and the column with the formula that will display the checkmark symbol or "YES" is Column H.

    Formulas I have tried:
    =IF((G2"YEAR")*(G2>=EDATE(YEAR(TODAY()),-5)),"YES","NO")
    =DATEDIF(G11,YEAR(TODAY()), "y")

    I feel like I am on the right track, but not sure how to tweak this. Would appreciate your help!!

      1. Yes that worked for the most part! Do you know what to add to it so I can choose what text to display if the formula is true or false instead of the words “true” or “false”?

  19. I would like to show service time in the format of years, months, and days. I can do this using a service start date and the following formula;
    =DATEDIF(B4,TODAY(),"y")&" Years "&DATEDIF(B4,TODAY(),"ym")&" Months "&DATEDIF(B4,TODAY(),"md")&" Days"

    However, I am not sure how to account for a potential break in service time such as an occasion where someone left the industry and returned years, months, or days later. Curious is there is a formula to subtract from the previous output a cell indicating a known break in service. Have not found anything to help online.

    Thank you!

    1. To find the duration in years, months, and days for two time periods, try the DATEDIF formula:

      =DATEDIF(0,D4-D3+D2-D1,"y")&" Years "&DATEDIF(0,D4-D3+D2-D1,"ym")&" Months "&DATEDIF(0,D4-D3+D2-D1,"md")&" Days"

      D1-start D2- end D3 -start D4 - end
      This should solve your task.

      1. I'll try it, Thanks!

  20. i want logbook of specific month with saturday and sunday being labelled as weekend with some specific color

  21. Hi, In my company a person gets a half day leave when he completes a month at the workplace. For example: If someone joins in March 10 and by April 10 he will be eligible for a half day leave. If he doesn't take that leave by May 10 he will have a full day (with April 0.5 + May 0.5). Can anyone suggest a formula to add 0.5 by the end of the month automatically?

  22. I fitted a cubic polynomial trendline to a dataset with dates on the x-axis and power on the y-axis. I (using a website) solved for x at a point on the plot. I converted the same date to a number using DATEVALUE assuming that is what the trendline was using for the polynomial equation. But while x solved to be 24505.24, the DATEVALUE yielded 45004. Any idea what is going on here?

  23. I have a date format as 20211123 and I want to convert it as 11-23-2021. Once I try changing it via date option in format fields option I am getting an error as #####. I have tried changing my system time but still it is not working.

  24. Hello!
    Is there a way to put a deadline on =today() so that it stops updating when it reaches that date?

  25. I have a formula for count Age that is:

    Year(Today())-Year(Date) this is right formula but when i use this formula like Year(Today()-Year(Date)) so, that formula is given number like 2,017 so please tell me

  26. Hi Alex,

    I want to convert the days before and after a date into 0 and 1, could you please help me with this?

  27. Hi Alex,

    Is there any way to get the present time by entering the time zone in google sheets?

    Waiting For your reply, Thanks.

  28. I have a received date, and the case should be completed within 30 calendar days.

    For example: Received 02/09/2023 - Due date ( 30) should be 03/11/2023 Formula = received date + 30.

    But 03/11/2023 ( is Saturday, or Sunday, or a Holiday.

    I need to calculate the due date, but in case the due date is Saturday, Sunday, or a Holiday, the due date should the prior business date.

    For example the due date will be 03/10/2023.

  29. Hi Alex, I Hope you are having a Great Day!

    Is there any way to use countifs function with multiple criteria but at least one should be match. can you solve it for me please?

    Thanks.

  30. is there a formula for the value how many days passed from the current month?
    I mean 8th of February would be 8
    14th of February would be 14..etc

  31. Hi, I have a date, written as 02-Feb-23 in excel. I want to remove 4 weeks and return the date on the closest Tuesday to 4 weeks prior to the original date.
    Is there a way to do this?

  32. Good day!

    Hello sir Alexander, may I ask for help on how to make formula like this

    for example
    today's date Jan. 30, 2023 but I want to put this on sheet like 01/30/2023. how to do this 01/30/2023? Thank you so much

    1. I have start date as 01-Jul-10 and end date as 28-feb-23 i need the number of years and balance of weeks.

      1. thank you so much sir for the reply, I just need to change the location. ty again

  33. Hi Alex,

    Is there any way to use the unique function to return multiple ranges in one column?

    If yes then please do the needful.

    Thanks.

      1. Hi Alex, Thanks for the help,

        Also, I want to know if there is any way to do multiple lookups and return various values from different tables and ranges.

        I am looking forward to hearing from you.

        Thanks.

  34. hai,
    My cell value is 31-Dec-10., i need to convert into dd/mm/yyyy format.

    1. Hi!
      I recommend reading this guide: How to change Excel date format and create custom formatting.
      If your date is written as text, convert the text to date in any of the suggested ways.
      You can convert more than 500 combinations representing dates in text format to regular Excel dates using Convert Text to Date Tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  35. I want 2 different formula to get the desired date from starting point of date,

    For example,
    Formula 1: FYE = 30/09/2022 (starting point of date) and i want first day of the 5th month from the FYE 30/09/2022 = 01/02/2023 (desired result of the formula)
    Formula 2: FYE = 30/09/2022 (starting point of date)and i want that my validity period should end in 16 Months from FYE and date should be displayed the last date of the 16th Month) = 31/01/2024 (desired result of the formula)
    Thank you in advance!

  36. I want a month cycle as 21st to 20th count as months 1st date to 30 or 31st whichever months come in, is there any custom formula for this?

  37. Hi Sir,

    I want to count 21st date as a 1st date of the month, and also same for the weeks can you do a custom formula for this?

  38. Hi Alex,

    I have a finance sheet comprised of columns such as:
    •payment due date, 3rd of each month (column A)
    •payment amount due (column b)
    •actual date when paid (column c)
    •actual amount paid (column d)
    •etc.

    Each row is a successive/consecutive month throughout the duration of the contract. I’m seeking assistance with both a formula and conditional formatting (hopefully without inputting 50+ duplicates for the latter).

    I have a cell specified to tally the # of missed payments via column D with =COUNTIF(range,”<1”). I now need to tally the # of late payments on/after 4th of the month. I’ve tried to utilize a full column range, with multiple criteria attempts of cell value/date in column C being later than cell value/date in column A.

    The conditional formatting is to highlight the rows of both: late payments, amber & no payment, red.

    What’s your advice?

  39. I’m trying to create a spreadsheet that will track how much vacation time an employee has at the current moment based on earning 0.219178082191781 hours a day or 1.534246575342466 a week. I also want the employee to be able to enter hours taken any day they take time off (this part I know how to do). I just do not know how to create a formula that well add the vacation time earned daily so they don’t have to go in each paycheck and update their available hours. Hopefully this makes sense.

  40. Hello,

    I have the below formula, but in this year (2023) it doesn't work, if change my system time to 2022 then it works, please help thank you in advance.

    =IF(MONTH(AB2)=MONTH(TODAY()-1),1,IF(MONTH(AO2)=MONTH(TODAY()-1),1,IF(MONTH(AL2)=MONTH(TODAY()-1),1,0)))

  41. When I enter a line in Excel, the Today() formula will give, todays date, which is ok, but if I open the Excel sheet tomorrow, it will gives tomorrow's date for every line. What can I add to the formula, to keep the previous dates, at the date of entry and if I enter a new line, it will give automatically today's date? Thanks for all your help in advance,

    1. Not sure you understand me well, but I also tried the following formula: =IF(a2"", IF(b2="", TODAY(), b2), ""), but Excel is not accepting this formula. The plan is as follows: if I enter something at cell A2, it should give todays date and it shouldn't update the date it should remain the same), if I open the excel sheet tomorrow again, it should give tomorrows date for that new line. Hopefully you have an idea to resolve this. Thanks for your help in advance,

  42. Dear Sir,
    When I combine data from 3 columns 125 25.02.2022 P.S. Sadar into one column output result comes just like that125 dated 222222 p.s Sadar
    I want result 125dated 25.02.2022 P.S. Sadar

  43. Dear Sir,
    1. I have the date format in Excel 02/25/2022 I want to get it into 25.02.2022

  44. What formula to use if I want the have the date of the beginning and end of the year to appear automatic and permanent formula to use without changing it yearly

    1. Hi!
      With the DATE function, you can display the start and end dates of the year. What formula you're talking about, I can't guess.
      DATE(YEAR(TODAY()),1,1)
      DATE(YEAR(TODAY()),12,31)

  45. How to make the formula for Year and month automatic , than showing the format is YYMM only.

    thank you for the advise,....

    2. example label need to put in 2212 only,
      but i used the formula =year(today())&month(today()) result is 202212 ,
      202212 need to convert to 2212 only how the formula

  46. In my Gantt chart project start date is 1-Jan,22 8 Am .

    Every day my project runs 8 hours (8am- 4pm)

    How can I calculate the end time with day & time?

    Like 1 day 3 hours need to end project.

    in this way output show 3 jan, 22 11am

    we need to follow the weekend also (like 2 Jan 22 weekend).

    in this way output show 3 jan, 22 11am

    at this moment use the below formula but for decimal day end day cant output the correct answer.

    =WORKDAY.INTL(U21-1,S21,"0000000",$F$1005:$F$1166)

      1. @alexander

        workday.int work properly but i want to calculate day with our for every project as per my estimated day & hour .

        In my Gantt chart project start date is 1-Jan,22 8 Am .

        Every day my project runs 8 hours (8am- 4pm)

        How can I calculate the end time with day & time?

        Like 1 day 3 hours need to end project.

        we need to follow the weekend also (like 2 Jan 22 weekend).

        in this way output show 3 jan, 22 11am

        another demo in below

        Daily Target Estimate DAYS Start Date End Date
        3400 1.96 11-Nov-22 12-Nov-22
        3400 1.90 13-Nov-22 14-Nov-22
        3400 0.71 15-Nov-22 15-Nov-22

        need to calculate time / hour with day

        1. Hi!
          If I understand the problem correctly, use the WORKDAY function to add working days, including holidays. Then calculate time and add hours.

          =WORKDAY(A1,1,C1) + (A1-INT(A1))+3/24

          Hope this is what you need.

  47. I would like to make a Task-completion list with "Date of Completion" in it.

    For that, if 3 of my sub-activities are completed in each task, the Status column will automatically show "Task completed"
    Then i would like to have the date on which it became " Task completed" in the next column. Ho to do that...?

    I tried but How do i get that date to remain like that forever... I used =IF(D1="Task Completed",TODAY(),"") but this gave me changing dates based on the day i opened the excel file... I need the date to be fixed once the cell becomes "Task Completed"...

    Pls help...

      1. Thank you so much... It really helped... :)

  48. Hello there, I just read through the page but didn't find a specific formula for what I am trying - I inherited a spreadsheet that has a column A of "Days old" (working days an item has been in our network) and column B is then the "Due date" of the item. The formula somebody added years ago is =SUM(TODAY()-B2)-1 if it is items after a weekend, and +1 on the end if items during the week that you add. I hope this makes sense(?) - I wanted a macro that can just automatically use todays date and the due date to know how many days the item has been in the network? (and even allowing for bank holidays if possible). I tried NETWORKDAYS but don't seem to be doing it properly? Anyway thanks alot for any help or response

  49. Dear Sir,
    I have some complex problem, In below statement how i can pull the updated balance of any particular date

    Say for example, If my date input is 19/01/22, I should get the result in other cell as $1,300,189.93
    If my date input is 20/01/22, I should get the result in other cell as $1,243,874.99
    If my date input is 26/01/22, I should get the result in other call as $1,100,163.18

    Date Particulars Vch No. Debit Credit Blanace
    01-Jan-22 Opening Balance 1,280,656.09 1,280,656.09
    19-Jan-22 Local Purchase A/c for Trading PV-53919 1,951.00 1,282,607.09
    19-Jan-22 Local Purchase A/c for Trading PV-53920 1,646.00 1,284,253.09
    19-Jan-22 Local Purchase A/c for Trading PV-53921 4,988.01 1,289,241.10
    19-Jan-22 Local Purchase A/c for Trading PV-53922 1,890.00 1,291,131.10
    19-Jan-22 Local Purchase A/c for Trading PV-53923 2,910.40 1,294,041.50
    19-Jan-22 Local Purchase A/c for Trading PV-53924 856.80 1,294,898.30
    19-Jan-22 Local Purchase A/c for Trading PV-53928 5,291.63 1,300,189.93
    20-Jan-22 Local Purchase A/c for Trading PV-53929 17,284.91 1,317,474.84
    20-Jan-22 Local Purchase A/c for Trading PV-53930 535.50 1,318,010.34
    20-Jan-22 Local Purchase A/c for Trading PV-53932 25,864.65 1,343,874.99
    20-Jan-22 EMIRATES ISLAMIC BANK A/c.No 014390 100,000.00 1,243,874.99
    24-Jan-22 EMIRATES ISLAMIC BANK A/c.No. 014391 100,000.00 1,143,874.99
    25-Jan-22 Local Purchase A/c for Trading PV-54177 23,355.59 1,167,230.58
    26-Jan-22 Local Purchase A/c for Trading PV-54178 1,011.53 1,168,242.11
    26-Jan-22 Local Purchase A/c for Trading PV-54180 17,003.27 1,185,245.38
    26-Jan-22 Local Purchase A/c for Trading PV-54181 1,156.68 1,186,402.06
    26-Jan-22 Local Purchase A/c for Trading PV-54182 15,552.81 1,201,954.87
    26-Jan-22 EMIRATES ISLAMIC BANK A/c.No.014392 100,000.00 1,101,954.87
    26-Jan-22 Local Purchase A/c for Trading PV-54174 1,791.69 1,100,163.18

    Kindly help me to fix this requirement

    With regards,

    Sunil Pinto.

      1. Sorry Sir,

        I thought it two different sites. My apologies. You solved my problem.

  50. How I get the latest previous date of the today. Below is my query.

    My today is 2/11/22. and latest previous date of today in the string is 30/10/2022

    How I Can design the function.

    01-10-22
    05-10-22
    07-10-22
    09-10-22
    18-10-22
    21-10-22
    26-10-22
    30-10-22
    05-11-22
    08-11-22
    12-11-22
    14-11-22
    16-11-22

    thanks & regards,

    Sunil Pinto.

      1. Super Sir,

        Thanks a lot, your function is solved my problem.

        Thank you very much for your prompt response as well

        1. Sir, Above function will not work if my today is 4/11/2022 and I must get the answer of previous day of today is 30/10/2022

          But if i put the function as "Vlookup(Date(2022,11,4),A1:A14,1,Ture) I get the answer as 04/11/2022 and its wrong answer.

          Please help me

          01-10-22
          05-10-22
          07-10-22
          09-10-22
          18-10-22
          21-10-22
          26-10-22
          30-10-22
          04-11-22
          05-11-22
          08-11-22
          12-11-22
          14-11-22
          16-11-22

          With regards,

          Sunil Pinto

          1. Hi!
            Dates in Excel are numbers. Subtract from the date you are looking for a number less than 1. VLOOKUP looks for the nearest smaller number. Please read the information in the link I gave you earlier.

            =VLOOKUP(DATE(2022,11,4)-0.0001,A1:A14,1,TRUE)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)