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.

Microsoft Excel provides a ton of functions to work with dates and times. Each function performs a simple operation and by combining several functions within one formula you can solve more complex and challenging tasks.

In the previous 12 parts of our Excel dates tutorial, we have studied the main Excel date functions in detail. In this final part, we are going to summarize the gained knowledge and provide links to a variety the formula examples to help you find the function best suited for calculating your dates.

The main function to calculate dates in Excel:

Get current date and time:

  • TODAY - returns today's date
  • NOW - returns the current date and time

Convert dates to / from text:

  • DATEVALUE - converts a date in the text format to date format
  • TEXT - converts a date to a text value

Retrieve dates in Excel:

  • DAY - returns the day of the month
  • MONTH - returns the month of a specified date
  • YEAR - returns the year of a specified date
  • EOMONTH - returns the last day of the month
  • WEEKDAY - returns the day of the week
  • WEEKNUM - returns the week number of a date

Calculate date difference:

  • DATEDIF - returns the difference between two dates
  • EDATE - returns a date N months before or after the start date
  • YEARFRAC - calculates the fraction of the year between 2 dates

Calculate workdays:

  • WORKDAY - returns a date N working days in the future or in the past
  • WORKDAY.INTL - returns a date N weekdays from the start date with custom weekends
  • NETWORKDAYS - returns the number of workdays between two dates
  • NETWORKDAYS.INTL - returns the number of workdays between two dates with custom weekends

Excel DATE function

DATE(year, month, day) returns a serial number of a date based on the year, month and day values that you specify.

When it comes to working with dates in Excel, DATE is the most essential function to understand. The point is that other Excel date functions not always can recognize dates entered in the text format. So, when performing date calculations in Excel, you'd better supply dates using the DATE function to ensure the correct results.

Here are a few Excel DATE formula examples:

=DATE(2015, 5, 20) - returns a serial number corresponding to 20-May-2015.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month.

=DATE(2015, 5, 20)-5 - subtracts 5 days from May 20, 2015.
Excel DATE formula examples

At first sight, the Excel DATE function looks very simple, however, it does have a number of specificities pointed out in the Excel DATE tutorial.

Below you will find a few more examples where the Excel DATE function is part of bigger formulas:

Excel TODAY function

The TODAY() function returns today's date, exactly as its name suggests.

TODAY is arguably one of the easiest Excel functions to use because it has no arguments at all. Whenever you need to get today's date in Excel, enter the following formula is a cell:

=TODAY()

Apart from this obvious use, the Excel TODAY function can be part of more complex formulas and calculations based on today's date. For example, to add 7 days to the current date, enter the following formula in a cell:

=TODAY()+7

To add 30 weekdays to today's date excluding weekend days, use this one:

=WORKDAY(TODAY(), 30)
Formula examples to get today's date in Excel

Note. The date returned by the TODAY function in Excel updates automatically when your worksheet is recalculated to reflect the current date.

For more formula examples demonstrating the use of the TODAY function in Excel, please check out the following tutorials:

Excel NOW function

NOW() function returns the current date and time. As well as TODAY, it does not have any arguments. If you wish to display today's date and current time in your worksheet, simply put the following formula in a cell:

=NOW()

Note. As well as TODAY, Excel NOW is a volatile function that refreshes the returned value every time the worksheet is recalculated. Please note, the cell with the NOW() formula does not auto update in real-time, only when the workbook is reopened or the worksheet is recalculated. To force the spreadsheet to recalculate, and consequently get your NOW formula to update its value, press either Shift+F9 to recalculate only the active worksheet or F9 to recalculate all open workbooks.

For more details, please see How to use NOW function in Excel.

Excel DATEVALUE function

DATEVALUE(date_text) converts a date in the text format to a serial number that represents a date.

The DATEVALUE function understands plenty of date formats as well as references to cells that contain "text dates". DATEVALUE comes in really handy to calculate, filter or sort dates stored as text and convert such "text dates" to the Date format.

A few simple DATEVALUE formula examples follow below:

=DATEVALUE("20-may-2015")

=DATEVALUE("5/20/2015")

=DATEVALUE("may 20, 2015")
DATEVALUE formula examples

And the following examples demonstrate how the DATEVALUE function can help with solving real-life tasks:

Excel TEXT function

In the pure sense, the TEXT function cannot be classified as one of Excel date functions because it can convert any numeric value, not only dates, to a text string.

With the TEXT(value, format_text) function, you can change the dates to text strings in a variety of formats, as demonstrated in the following screenshot.
Excel TEXT formula examples

Note. Though the values returned by the TEXT function may look like usual Excel dates, they are text values in nature and therefore cannot be used in other formulas and calculations.

Here are a few more TEXT formula examples that you may find helpful:

Excel DAY function

DAY(serial_number) function returns a day of the month as an integer from 1 to 31.

Serial_number is the date corresponding to the day you are trying to get. It can be a cell reference, a date entered by using the DATE function, or returned by other formulas.

Here are a few formula examples:

=DAY(A2) - returns the day of the month from a date in A2

=DAY(DATE(2015,1,1)) - returns the day of 1-Jan-2015

=DAY(TODAY()) - returns the day of today's date
Examples of using the DAY function in Excel

Excel MONTH function

MONTH(serial_number) function in Excel returns the month of a specified date as an integer ranging from 1 (January) to 12 (December).

For example:

=MONTH(A2) - returns the month of a date in cell A2.

=MONTH(TODAY()) - returns the current month.

The MONTH function is rarely used in Excel date formulas on its own. Most often you would utilize it in conjunction with other functions as demonstrated in the following examples:

For the detail explanation of the MONTH function's syntax and plenty more formula examples, please check out the following tutorial: Using the MONTH function in Excel.

Excel YEAR function

YEAR(serial_number) returns a year corresponding to a given date, as a number from 1900 to 9999.

The Excel YEAR function is very straightforward and you will hardly run into any difficulties when using it in your date calculations:

=YEAR(A2) - returns the year of a date in cell A2.

=YEAR("20-May-2015") - returns the year of the specified date.

=YEAR(DATE(2015,5,20)) - a more reliable method to get the year of a given date.

=YEAR(TODAY()) - returns the current year.
Examples of using the YEAR function in Excel

For more information about the YEAR function, please see:

Excel EOMONTH function

EOMONTH(start_date, months) function returns the last day of the month a given number of months from the start date.

Like most of Excel date functions, EOMONTH can operate on dates input as cell references, entered by using the DATE function, or results of other formulas.

A positive value in the months argument adds the corresponding number of months to the start date, for example:

=EOMONTH(A2, 3) - returns the last day of the month, 3 months after the date in cell A2.

A negative value in the months argument subtracts the corresponding number of months from the start date:

=EOMONTH(A2, -3) - returns the last day of the month, 3 months before the date in cell A2.

A zero in the months argument forces the EOMONTH function to return the last day of the start date's month:

=EOMONTH(DATE(2015,4,15), 0) - returns the last day in April, 2015.

To get the last day of the current month, enter the TODAY function in the start_date argument and 0 in months:

=EOMONTH(TODAY(), 0)
EOMONTH formulas to get the last day on the month in Excel

You can find a few more EOMONTH formula examples in the following articles:

Excel WEEKDAY function

WEEKDAY(serial_number,[return_type]) function returns the day of the week corresponding to a date, as a number from 1 (Sunday) to 7 (Saturday).

  • Serial_number can be a date, a reference to a cell containing a date, or a date returned by some other Excel function.
  • Return_type (optional) - is a number that determines which day of the week shall be considered the first day.

You can find the complete list of available return types in the following tutorial: Day of the week function in Excel.

And here are a few WEEKEND formula examples:

=WEEKDAY(A2) - returns the day of the week corresponding to a date in cell A2; the 1st day of the week is Sunday (default).

=WEEKDAY(A2, 2) - returns the day of the week corresponding to a date in cell A2; the week begins on Monday.

=WEEKDAY(TODAY()) - returns a number corresponding to today's day of the week; the week begins on Sunday.
Excel WEEKDAY formulas to return the day of the week

The WEEKDAY function can help you determine which dates in your Excel sheet are working days and which ones are weekend days, and also sort, filter or highlight workdays and weekends:

Excel DATEDIF function

DATEDIF(start_date, end_date, unit) function is specially designed to calculate the difference between two dates in days, months or years.

Which time interval to use for calculating the date difference depends on the letter you enter in the last argument:

=DATEDIF(A2, TODAY(), "d") - calculates the number of days between the date in A2 and today's date.

=DATEDIF(A2, A5, "m") - returns the number of complete months between the dates in A2 and B2.

=DATEDIF(A2, A5, "y") - returns the number of complete years between the dates in A2 and B2.
DATEDIF formulas to calculate the date difference in Excel

These are just the basic applications of the DATEDIF function and it is capable of much more, as demonstrated in the following examples:

Excel WEEKNUM function

WEEKNUM(serial_number, [return_type]) - returns the week number of a specific date as an integer from 1 to 53.

For example, the below formula returns 1 because the week containing January 1 is the first week in the year.

=WEEKNUM("1-Jan-2015")

The following tutorial explains all the specificities on the Excel WEEKNUM function: WEEKNUM function - calculating week number in Excel.

Alternatively you can skip directly to one of the formula examples:

Excel EDATE function

EDATE(start_date, months) function returns the serial number of the date that is the specified number of months before or after the start date.

For example:

=EDATE(A2, 5) - adds 5 months to the date in cell A2.

=EDATE(TODAY(), -5) - subtracts 5 months from today's date.

For a detailed explanation of EDATE formulas illustrated with formula examples, please see: How to use EDATE function in Excel.

Excel YEARFRAC function

YEARFRAC(start_date, end_date, [basis]) function calculates the proportion of the year between 2 dates.

This very specific function can be used to solve practical tasks such as calculating age from date of birth.

Excel WORKDAY function

WORKDAY(start_date, days, [holidays]) function returns a date N workdays before or after the start date. It automatically excludes weekend days from calculations as well as any holidays that you specify.

This function is very helpful for calculating milestones and other important events based on the standard working calendar.

For example, the following formula adds 45 weekdays to the start date in cell A2, ignoring holidays in cells B2:B8:

=WORKDAY(A2, 45, B2:B85)

For the detailed explanation of WORKDAY's syntax and more formula examples, please check out:

WORKDAY function - add or subtract workdays in Excel

Excel WORKDAY.INTL function

WORKDAY.INTL(start_date, days, [weekend], [holidays]) is a more powerful variation of the WORKDAY function introduced in Excel 2010.

WORKDAY.INTL allows calculating a date N number of workdays in the future or in the past with custom weekend parameters.

For example, to get a date 20 workdays after the start date in cell A2, with Monday and Sunday counted as weekend days, you can use either of the following formulas:

=WORKDAY.INTL(A2, 20, 2, 7)

or

=WORKDAY.INTL(A2, 20, "1000001")

Of course, it might be difficult to grasp the essence from this short explanation, but more formula examples illustrated with screenshots will make things really easy:

WORKDAY.INTL - calculating workdays with custom weekends

Excel NETWORKDAYS function

NETWORKDAYS(start_date, end_date, [holidays]) function returns the number of weekdays between two dates that you specify. It automatically excludes weekend days and, optionally, the holidays.

For example, the following formula calculates the number of whole workdays between the start date in A2 and end date in B2, ignoring Saturdays and Sundays and excluding holidays in cells C2:C5:

=NETWORKDAYS(A2, B2, C2:C5)

You can find a comprehensive explanation of the NETWORKDAYS function's arguments illustrated with formula examples and screenshots in the following tutorial:

NETWORKDAYS function - calculating workdays between two dates

Excel NETWORKDAYS.INTL function

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) is a more powerful modification of the NETWORKDAYS function available in Excel 2010 and later. It also returns the number of weekdays between two dates, but lets you specify which days should be counted as weekends.

Here is a basic NETWORKDAYS formula:

=NETWORKDAYS(A2, B2, 2, C2:C5)

The formula calculates the number of workdays between the date in A2 (start_date) and the date in B2 (end_date), excluding the weekend days Sunday and Monday (number 2 in the weekend parameter), and ignoring holidays in cells C2:C5.

For full details about the NETWORKDAYS.INTL function, please see:

NETWORKDAYS function - counting workdays with custom weekends

Hopefully, this 10K foot view on the Excel date functions has helped you gain the general understanding of how date formulas work in Excel. If you want to learn more, I encourage you to check out the formula examples referenced on this page. I thank you for reading and hope to see you again on our blog next week!

Table of contents

549 comments

  1. 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!!

  2. 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!

    • 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.

      • I'll try it, Thanks!

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

  4. 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?

  5. 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?

  6. 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.

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

  8. 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

  9. Hi Alex,

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

  10. Hi Alex,

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

    Waiting For your reply, Thanks.

  11. 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.

  12. 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.

  13. 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

  14. 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?

  15. 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

  16. 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.

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

  18. 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!

  19. 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?

  20. 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?

  21. 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?

  22. 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.

  23. 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)))

  24. 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,

    • 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,

  25. 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

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

  27. 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

    • 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)

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

    thank you for the advise,....

  29. 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)

      • @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

        • 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.

  30. 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...

  31. 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

  32. 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.

  33. 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.

      • Super Sir,

        Thanks a lot, your function is solved my problem.

        Thank you very much for your prompt response as well

        • 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

          • 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)

  34. Challenge!!!!

    I have have two types of service 1000$ For 30 days and 1500$ for 30 Days, my Customer pays me 1000$ on 08-10-2022 and subscribe for 1000$ Package and at the end of the month on 31-10-2022 pays 1500$ and subscribe the other package.

    I want to know how much should I charge him on 31-10-2022 and 30-11-2022

  35. Hi, How do I use the If function to date range to bring back another date for example: Date date from: 30/06/2013 to 29/06/2014 brings back 30/06/2016, and Date date from: 30/06/2014 to 29/06/2015 brings back 30/06/2017, Date date from: 30/06/2015 to 29/06/2016 brings back 30/06/2018, Date date from: 30/06/2016 to 29/06/2017 brings back 30/06/2019, etc. and Date date from: 01/02/2019 to onwards - brings back exactly 2 years, or see below:

    Date ranges:

    From To brings back 1 & brings back 2
    30/06/2013 29/06/2014 30/06/2016 30/06/2020
    30/06/2014 29/06/2015 30/06/2017 30/06/2021
    30/06/2015 29/06/2016 30/06/2018 30/06/2022
    30/06/2016 29/06/2017 30/06/2019 30/06/2023
    30/06/2017 29/06/2018 30/06/2020 30/06/2024
    30/06/2018 31/01/2019 30/06/2021 30/06/2025
    01/02/2019 onwards 24 months 6years

    • From To Bring back 1 Bring back 2
      30/06/2013 29/06/2014 30/06/2016 30/06/2020
      30/06/2014 29/06/2015 30/06/2017 30/06/2021
      30/06/2015 29/06/2016 30/06/2018 30/06/2022
      30/06/2016 29/06/2017 30/06/2019 30/06/2023
      30/06/2017 29/06/2018 30/06/2020 30/06/2024
      30/06/2018 31/01/2019 30/06/2021 30/06/2025
      01/02/2019 onwards 24 months 6 years from date

      Examples of dates Bring back 1 Bring back 2
      11/08 2013 30/06/2016 30/06/2020
      01/12/2017 30/06/2020 30/06/2024
      01/11/2022 01/11/2024 01/11/2028

  36. I need to enter a formula that involves two columns (one is a date column and the other column has a set dollar amount of $25.00 IF a date was entered into the first column), I assume this would be done using the IF function but I cannot figure out the proper way to enter it. Can anyone offer assistance?

  37. How can I display a null or blank value in a cell when I use year() function? because there will be cases where one of the selected cells for year() function doesn't have a date.

  38. Good day!

    May I ask for an assistance on how to solve the problem below

    In a sheet, dates are as follows:
    1. 1981/12/15
    2. 1978/12/02
    3. 01/24/2003
    4. 08/18/1980
    5. 05/25/1983
    6. 1981/03/23

    but the moment, i change the format to "YYYY/MM/DD" numbers 3-5 weren't change.
    What should I do in order for the dates to be in the same format?

    Thank you

  39. Hello, I have 7 cells with the days of the week populated, 28, 29, 30, etc... I want to use the first cell as a starting point for the other 6 cells and retuning that day of the week. So A1=28, A2=29. I just did a simple A2=A1+1 formal, but if a month has 31 days, I get 32, 33, or 34 if A1=30 or 31. I'm a little stuck. Thanks

  40. Hi, How do I calculate a future year and date, but the year must change to 3 years, but the date must always bring back 30/06/future year. Example: 10/04/2012 must return 30/06/2015. The month and date must always be 30/06. Thank you.

      • Thank you very much. I have been battling with this, and you made it so easy. I appreciate your help

        • Hi, can I have two arguments for the same sell - I would like to use =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)) and =DATE(YEAR(C2)+3,6,30) together, For Example if the year is 2015, then it must first check if the date is before 2018 to apply =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)), but if its after 2018, (01/03/2019 - then its should apply =DATE(YEAR(C2)+3,6,30) - basically having both formulas in 1 sell?

          Thanks in advange

  41. Hi,

    I have a timeline with moving dates, what I'm trying to do is to develop a formula that is able to determine how many days are on specific quarters of a year so for example:

    This study will have a start date: Feb 14 2022, and a finish date: May 17 2022
    The study cost was $3000

    Considering the previous info, I want to know how many days this study has on the 1st, 2nd, 3rd and 4rd quarter of the year so that I can then split the total cost of the study per each quarter.

    The quarters of the year are always de same: 1st quarter is Jan-Feb-March, the second is April-Jun-Jul and so on.

    Regards,

    • Hello!
      Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  42. Hello, i want to print to excel the dates between 1/1/2020 and 31/5/2022 but every day has to be printed 24 times. I mean ,
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    .
    .
    .
    .
    .
    .
    .
    2/1/2020
    2/1/2020.
    .
    .
    .
    Any help please?

  43. Hi, I would like to know the formula on how to set up specific dates with a pattern, like for example,

    08 August 2020
    10 August 2020
    12 August 2020
    15 August 2020
    17 August 2020
    19 August 2020

    and repeats the process up to present day. Your response is very much appreciated. Thank you!

      • Hello Alexander, thank you for the reply. Allow me to emphasize my example. The pattern is every Monday, Wednesday and Saturday only, and those are the dates that fall within those days. I'd like to know the idea behind it to ease my task of filling the rest of the dates up to present. Hope you could provide answer(s). Thank you.

  44. Hello,

    Very Complex question here I believe and I'll try my best to explain the situation. In short Im trying to figure out how to return a value of a widget in a separate column, of adjacent cells, when I select a given date from a drop down list while also being able to toggle between any other date to return the value of the widget based on that date that is selected. I update the value of the widgets manually every monday and they are listed vertically in values from F5:F479. The dates are arranged horizontally from F4:XFD. So as i update the values every monday the values change in columns by 1 every time (which represents 1 week basically) but im just adding to the previous weeks list of values so that i can monitor the increase or decrease in those values over time. So column "A" contains the name of the widget, Columns "E" through the end of the entire worksheet contain the listed weekly values, Column "D" is the column I want to use to show me the value of the adjacent widget based on the date i select from the drop down list. I have defined names for certain cells and arrays to utilize index match functions as well, i just cant figure out how to combine all the different possible functions of excel to get this to work. Any help is greatly appreciated. Would love to set up a meeting even to go over this if at all possible. Thank you!!

  45. Hi all,

    I would like to have a formula where the end result (as a date) is 1 month after the value selected, but I want it to be corrected to FRIDAY of that week, every time.
    For example,
    the value selected is: 25MAY22
    the end result that we want: 24JUN22
    ===>25MAY22 + 1 month (22JUN22 - which is Wednesday) and to the Friday of that week --> 24JUN22

    Is that possible to do that as a formula?

      • Hey Alexander,

        Thank you for you quick reply, and yes the formula worked!

        By 1-month, I actually meant 4 weeks;
        25MAY
        1 week later: 01JUN
        2 weeks later: 08JUN
        4 weeks later: 22JUN

        I hope this makes more sense, but regardless, thanks again.

  46. I'm not sure if this relates specifically to any of the month formulas, but I'm trying to calculate vacation accrual each month. How can I have a specific value added to a cell automatically on the first day of each month? So, add 15 hours every 1st of the month to one cell, to continually show my vacation balance. Thanks!

  47. I have 3 columns representing dates of vaccination (1st dose, 2nd dose, 3rd dose dates). I have another column with visit dates. I must compare dates of vaccination to visit date- to see how many doses they had prior to the visit date. How can i do this. some time the first dose can be blank as they are unvaccinated.

    • Hello!
      If I got you right, the formula below will help you with your task:

      =SUM((A1:C1 < D1)*(A1:C1 < > ""))

      D1 - visit date
      A1:C1 - dates of vaccination

  48. Hi, is there a way to get the nth day based on a specified start month?

    Day 0 is May 1, 2022
    Day 1 is May 2, 2022, and so on.

    If I were to input Jun 15, 2022, can it provide me what Day it is?

    Thanks in advance!

  49. I am trying to figure out if there's a way to get a "live count" on a spreadsheet. For example, I deal with people with terminal illnesses. I'm trying to track their lifespan from the date of the diagnosis to see how long their lifespan is. I have it figured out once they pass away. Is there a way to have a formula that will update each day that passes from the "start date" (which is the diagnosis date)... but there is no end date yet?

    Example E1 is the diagnosis date 1/20/2022, F1 is blank because there's no deceased date yet, and G1 will show "5" because today is the 5th day. Then tomorrow, it will automatically roll to "6" for the 6th day. Is this even possible to do in excel?

  50. Hi guys, I need help with this.

    Trying to set today date into a cell with 25.12.2021

    I keep getting 12/25/2021 when I use TODAY()

    Any help?

      • Hi Alexander,

        Can a formula do that without going to custom formatting?

        I'm trying to use a bot to set the text in the cell. This cell is also referenced by some other cells.

        If there is a formula for this I will really appreciate.

        • Hi!
          I guess you haven't read the manual from the link I gave you. There is a paragraph: How to change date format in Excel. You don't need to apply conditional formatting.
          By the way, no Excel formula can change the cell format. This can be done either manually or using a VBA macro.

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 :)