Jun
10

Excel date functions - formula examples of DATE, TODAY, etc.

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:

Convert dates to / from text:

Retrieve dates in Excel:

Calculate date difference:

Calculate workdays:

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.

If your goal is to get a static timestamp in your Excel sheet, then use one of the following shortcuts:

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.

To make the NOW() function automatically update every second or so, a VBA macro is needed (a few examples are available here).

Excel DATEVALUE function

DATEVALUE(date_text) converts a date in the text format to a serial number that Microsoft Excel recognizes as 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 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

You can find more DAY formula examples by clicking the following links:

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: Calculating days of week in Excel (WEEKDAY function).

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: Add or subtract months to a date with EDATE function.

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 and also available Excel 2013 and 2016.

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 the modern versions of Excel 2010, Excel 2013 and Excel 2016. 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!

159 Responses to "Excel date functions - formula examples of DATE, TODAY, etc."

  1. yasmeen says:

    hi

    I want to know the upcoming specified date after numbers of days, like I want to know of project ending

    project duration 20 days
    project starts today

    so when project will be finished in which date

    thanks
    Yasmeen

  2. Hi Yasmeen,

    You simply add 20 days to today's date:
    =TODAY()+20

    If the project duration numbers are stored in other cells, then you can refer to those cells in your formula, for example =TODAY()+B1 where B1 is Project duration.

  3. Florence says:

    Hi,

    I need to know the formula to use if I want to get the earliest date from a row of dates. Example, I have 4 different dates, ( May 1, 2016 ; Sept. 2, 2015 ; August 19, 2018 and Sept 8, 2018). I need to get the earliest date from these 4 dates and it should show up. What formula do I need to use. Thanks.

  4. Brook says:

    How can I enter the date with something like a TODAY function, but have the result fixed as it was on the day of entry, instead of volatile result that changes each time I open or refresh file?

    • Hi Brook,

      There is no function in Excel that would let you enter the current date as a fixed unchangeable time stamp. You will need a VBA macro for this or you can use the shortcut Ctrl+;

  5. mickybraham says:

    Hi,

    I have used conditional formatting in Column N so that when today's date is past the date in each cell it changes to bold, red text.

    In column P I have used conditional formatting to say that if I have tended to my outdated task as the following: equal to 'Y' is Green, equal to 'No answer' is yellow, equal to 'n' is red.

    My question is:

    If I enter either 'Y' or 'No answer' or 'N' into any cell in column P can this change the font in the corresponding cell in column 'N' back to regular, black text.

    Hope you can help

    Thanks
    Micky

  6. mickybraham says:

    Thank you!

    It worked but P3 changes N2. Do you know what I've done wrong so that P3 changes N3 and so on

    Thanks
    Micky

    • Micky,

      Make sure that the cell referenced in the formula corresponds to the first row in the rage to which the rule apples.

      I.e. if your apply the rule beginning with row 2, use the formula I provided, if you apply the rule beginning with row 1 then replace P2 with P1 in the formula, and so on.

  7. mickybraham says:

    Thank you its working now!

  8. Michael says:

    I wanted to set a now date for when i open the sheet to calculate the date and add another value to another cell if the date is greater than a set date of each month i.e late fee applied in cell B3 if date is over the 5th

    • Michael says:

      Forgot to mention that late fee applied in cell b3 if date is over the 5th but only if there is no value in A3.
      A3=Payment made B3=Late fee If date is Now date is greater then 5th of each month

  9. Tony says:

    Is there a way to write a formula toe present yesterday to today? example present 8/13/2015 to 8/14/2015.

    • Hi Tony,

      Sorry, I do not exactly understand the task. Do you want to "label" the dates as "present" (i.e. display the word "present" in another column in the same row) or are looking for something else?

  10. Apurve says:

    I am looking for a formula which can add two years from the reference column date (DD-MM-YY) and and subtract three months from that, show up in next column.

    Is it possible to color a the same output column with two different color based on starting of the date in this column and three months past from that day.

    Thanks,
    Apurve

  11. Meer Masood says:

    Hi,
    I am looking for a formula which can give me the count for difference between start date and today's date which excludes weekend. For ex, if i want to see diffenence between 15th Sep 2015 and 21st sep 2015, i should get result as 5 excluding sunday.

    Thanks,
    Meer

  12. Hi Meer,

    You can use the NETWORKDAYS function, like =NETWORKDAYS(A2, B2), where A2 is the start date and B2 is the end date.

  13. an says:

    hi,

    I'm looking for a formula in which computes the current year minus the previous year. I'm creating a table in which automatically compute the number of years or months.

    example:

    employee 1 starts: August 1, 2009 - current year
    Employee 2 starts: Sept 1, 2015 - current year

    Thank you,

  14. gordon says:

    Hi, I need to have (todays date - 3) calculated in mmddyy format and inserted into a text field (e.g. "ABC/mmddyy_received". I will be really impressed if that is possible.

    • Hello Gordon,

      You can use the TEXT function to convert the current date to text in any desired format, and concatenate the returned value with other text, e.g.:

      ="ABC/"&TEXT(TODAY(),"mmddyy")&"_received"

  15. Esther says:

    hi am looking for a formula that I can use in excel to highlight customer details on their payment days e.g if a customer make payments on Saturday a highlight on her details

    • Vijaykumar Shetye says:

      Dear Esther,

      (1) To highlight the cells whose dates math the weekday of any given day, go to Home - Conditional formatting - New rules - Select a rule type - Use a formula to determine which cells to format - Edit the rule description
      In Edit the rule description, enter the below formula
      =WEEKDAY(TODAY())=WEEKDAY(A1)
      In Format - Fill, select the required colour.

      Is this what you want to do?

      (2) In case you want to highlight cells which matches the date on any given day, then type the below formula in Conditional Format.
      =a1=today()

      Regards,

      Vijaykumar Shetye, Goa, India

  16. John says:

    Is there a formula that will allow me to open an excel file and it first of all create a NOW() DATE and then project a WEEK COMMENCING DATE for 4 weeks later

  17. Mohd Shabbir says:

    Hi,
    I want to create a employee attendance sheet with below details,
    1. Set weekend by our choice any day of week like (Fri and Sat) instead of Sat and Sun
    2. Holiday
    3. auto changeable date if we select month and year

    Please advise accordingly

  18. JOE says:

    Hi,
    Is there a formula for counting workday from start date to end date, where end date =today() until an exact date is entered. Thanks.

  19. MOhamed Ibrahim says:

    I have Grade in excel needs to add next schedule date
    for example
    Grade A - add 180 days to the previous date (10/11/2015 + 180 days)
    Grade B - add 365 days to the Previous date (10/10/2014 + 365 days)

  20. James says:

    I need a function that will return a value only if the specified day of the month is within a week ending column.
    For example, I have a weekly cash flow forecast and I always pay my utility bill of $500 on the 15th of the month. I want a formula that will only return $500 when my week ending column includes the 15th of the month.
    Please can you let me know if there is a formula I can use.
    Thanks

  21. Killian says:

    Hi,

    Im trying to use If(a2>0,Today(),"") but trying to make it so that I may update it when I update the A2 cell. how can I do that?

    Killian,

  22. laxman says:

    Hi,

    Want to call the date based on leave applied in From Date and To Date column to know the exact date which he applied the leave for.

    Employee IDs - A1:001, A2: 002, A3: 003....
    Date- B1:Jan 1, C1:Jan 2, D1:Jan 3 ..... till 31st Jan

    Emp ID
    1-Jan 2-Jan 3-Jan 4-Jan From Date To Date
    Casual Leave(CL)
    001 CL p p p 1-Jan 1-Jan
    002
    003
    004
    005

    Appreciate your quick help.

    Regards,

    Laxman

  23. Tariq says:

    Hi,

    I failed to calculate via Index function the "Min" of dates array;

    =INDEX(MIN('Dates'!N15:N35),MATCH(A4,'Students'!B15:B35,0))

    Result is #N/A, though values are available in the date type and both respective cells.

    Can you please help? Thanks.

    Regards,
    Tariq

  24. Eduardo says:

    Hi,

    I have a list of dates from: January 2013 to december 2016
    I want to call the current date of these list accordingly the current month date.

    e.g Now I would need to call from this list date: 20.11.15 since the next date 18.12.2015 is not present yet.

    Cn you help me how to add a formula for this?

    thanks in advance!

  25. MRZA says:

    In ticketing systems we have three type of ticket for different SLA (Service level agreement),deadline for each type of ticket is different related to agreement between Customer and vendor. I want to calculate workday between two date with excluding weekends and holidays, some ticket have suspend status. for instance they become park or suspend and after a while they become active,in this situation I want to exclude suspend period and + weekends and holidays and calculate workday as our deadline,
    I was wondering that what your suggestion is

  26. ANAND JULU says:

    hi

    i want to create upcoming date auto change by formulas
    for example today 10-12-2015 so next column next five year auto add than resulted change from 09-12-2020

    so plz help what is the formulas set with excel..

  27. Abi says:

    I want excel to fill on completion date if I change status to complete. What formula should I use?

  28. Manju says:

    If I entered date in Cell A1, cell C1 should Show today-A1 cell, if I entered date in B1 C1 cell should show Completed if A1 and B1 is blank C1 should show Don't worry.
    Please let me know formula for this details

  29. Manju CV says:

    If I entered date in Cell A1, cell C1 should Show today-A1 cell, if I entered date in B1 C1 cell should show Completed if A1 and B1 is blank C1 should show Don't worry.
    Please let me know formula for this details

  30. Manju says:

    Hi
    If cell A1 and B1 have date it should show completed, but it showing number of days pending, please clarify this!

    • Manju,

      In your first message, the conditions were stated in this way: "If I entered date in Cell A1, cell C1 should Show today-A1 cell; if I entered date in B1 C1 cell should show Completed". The formula checks the conditions in this order, and as soon as the first condition is met, other conditions are not checked.

      Now, it you want to show "completed" when A1 and B1 have dates in them, use this one:

      =IF(AND(A1<>"", B1<>""), "Completed", IF(A1<>"",TODAY()-A1, "Don't worry"))

      If you want the formula to show "Completed" when there is a date in B1, regardless of whether there is or there is no date in A1, use this one:

      =IF(B1<>"", "Completed", IF(A1<>"",TODAY()-A1, "Don't worry"))

  31. Manju says:

    hi
    thanks!!!

  32. MukundhaRao says:

    Dear Sir / Madam

    Please Clarified Me , How Do The Calication In Excel

    No Saledate Km LastServiceDate Last Service Type
    1 01/02/2015 100Km 25/02/2015 1St Free
    2 02/05/2013 5000 Km 01/05/2014 2Nd Free Service

    Dear Madam / Sir

    1St Free From Sale Date To 60 Days
    2Nd Free Service From The Sale Date To 330 Days Or 1500 Km
    3Rd Free Service From Sale Date To 700 Days Days Or Below 7000 Km

    After Complete 3Rd Free Service It Will Show Paid Service ,

    Please Explain Me
    I Need Sale Date To Service And Last Service Date To Up Comeing Service Type

    MyNo Was - 9611786004
    Please Help Me How To Do.....

  33. MukundhaRao says:

    Dear Madam

    Please Check I Will Send The Data To Your Emil Id , Which Your Provided ID
    My Personal Mail Id Was (MukundhaRaorm13@gmail.com)

    Please Check Madam

  34. vivek says:

    I have a date as 01/20/16 i need to change it to 01/20/2016? How do i do that?
    I tried using Date/Year function but no luck

  35. Goran says:

    Dear Madam/Ser,

    Which calculationcould I use for:
    I have a production of some product of 34 h, and other product 6 h
    The working process is in two shift, output/shift is 12 h.
    I would like to enter the start time for product1, the calculation to calculate the end date of production of product1, excluding the weekends and non woring time (from 22:00 to 06:00 next day). The production of product2, start at the end time of product1 and calculate on sam principle the end time of production for product2.

  36. vikas says:

    I would like to know how we can retrieve the date for 3rd sat of every month for the year 2015

  37. Lloyd Freeman says:

    Hi,

    I have a spreadsheet that has a record of all our stock. There is a column that indicates how many days old each item of stock is. However at the moment I have to manually change the age so for example if it was 2 days old yesterday I would have to change it to 3 today then 4 tomorrow etc. What I would like to know is if there is a formula that you can get this information to update automatically each day so that it just ticks over the older the stock becomes. Thanks

  38. Rajat Arora says:

    Hi,

    Could you please share the formula to calculate the Last Present Date.

  39. Joanna says:

    If I have a row of dates in row 2 starting from column B, and wanted to highlight the current date above the row. I could format row 1 using:

    =B$2=today() formatted to red.

    How could I ensure that a date is highlighted on a Tuesday+ if those dates were only week commencing dates?

  40. Lisa says:

    Hello,

    I'm using the following to determine the hours/minutes between the dispatch date and the run date the report. The problem I'm running into, I have custom hours during the business day of 7:30a to 3:30p. How do I incorporate the custom hours, instead of using the 9am to 5pm this formula uses? Thank you, Lisa

    =IF(NETWORKDAYS(H6,$B$3,Holiday)-1=0,HOUR(MOD(H6-$B$3,1))&" Hour "&MINUTE(MOD(H6-$B$3,1))&" Minutes",NETWORKDAYS(H6,$B$3,Holiday)-1&" Days "&HOUR(MOD(H6-$B$3,1))&" Hour "&MINUTE(MOD(H6-$B$3,1))&" Minutes")

  41. CELLEX says:

    I need a formula which when entered in a cell of a Colum will automatically add date and time base on the present date and time on the rows down the Colum as I populate other cells in an excel sheet.

  42. PRAVEEN SHARMA says:

    20180502
    20170101
    20180301
    20190802
    20170901
    20180601
    20170201
    20160601
    how to convert in to date yyyy-mm-dd 16'oct convert in 10-2016

    • Munawwer khan says:

      please help me,

      i have online software date format (14/03/2016 3:07 PM) i need to convert this format (Mar/14/2016)

      please help

      • Vijaykumar Shetye says:

        Dear Munawwer Khan,

        Select the cell and go to Home - Number - Custom - Type,
        and enter the below format
        mmm/dd/yyyy.

        The value of the cell will not change, but it will be displayed in the type of format you require.

        Vijaykumar Shetye, Goa, India

  43. Scott says:

    Hi,

    I am interested in a date formula that will allow me to enter data into other fields and then having that date "stamped" when the data was entered

    example: in cell A1 have a name typed and then in A2 have today's date appear (and not change).

    Let me know

    Thank you

  44. Mahesh says:

    Hi,

    I want to identify with the ageing of the current time by considering it as non communicating 6 hours

    Ex: 18-03-2016 12:28 (>6 hours forumla should show as "communicating")
    18-03-2016 12:28 (6< hours forumla should show as "noncommunicating")

    Thanks

    • Mahesh says:

      To make it more simple.

      Here is the date and time

      18-03-2016 12:37

      (=IF(L2>=TIME(20,59,59)+ TIME(21,0,0),"communicating","noncommunicating")

  45. Munawwer khan says:

    hi everyone :)

    i have date format in see (1)
    need convert to this format in excel see (2)

    1) 16-03-2016 5:46 PM
    2) 3/12/2016 1:13:00 AM

    • Vijaykumar Shetye says:

      Go to Home - Format Cells - Number Type, and
      Change the format of the cell to
      d/mm/yyyy h:mm:ss AM/PM

      Vijaykumar Shetye,
      Goa, India

  46. Michael says:

    Currently, I'm using the 'weekday' formula in which the dates on the left give me the dates on the right.

    =B2-WEEKDAY(B2-6)
    Friday, March 18, 2016 11-Mar
    Saturday, March 19, 2016 18-Mar
    Sunday, March 20, 2016 18-Mar
    Monday, March 21, 2016 18-Mar
    Tuesday, March 22, 2016 18-Mar
    Wednesday, March 23, 2016 18-Mar
    Thursday, March 24, 2016 18-Mar
    Friday, March 25, 2016 18-Mar
    Saturday, March 26, 2016 25-Mar
    Sunday, March 27, 2016 25-Mar

    I'm trying to tweak the equation so that the pattern will look like the following: Basically, I'm trying to shift the dates on the right up by two (i.e. 3/19 will now reflect 3/11, 3/20 will now reflect 3/11, 3/26 will reflect 3/18, 3/27 will now reflect 3/18)

    Friday, March 18, 2016 11-Mar
    Saturday, March 19, 2016 11-Mar
    Sunday, March 20, 2016 11-Mar
    Monday, March 21, 2016 18-Mar
    Tuesday, March 22, 2016 18-Mar
    Wednesday, March 23, 2016 18-Mar
    Thursday, March 24, 2016 18-Mar
    Friday, March 25, 2016 18-Mar
    Saturday, March 26, 2016 18-Mar
    Sunday, March 27, 2016 18-Mar

    Any assistance on how to formulate that would be greatly appreciated.

  47. Syed Raheemuddin says:

    I have once Excel file in which i have 52 columns considering as weeks of the year. Then if the cell is equal to current date then i have to display the value from other sheet.Please help

    • Vijaykumar Shetye says:

      Dear Syed Raheemuddin,

      I have not understood your question. Please explain it in detail.

      If a cell is equal to current date, then how will you display the value form another sheet in the cell?

      When posting a question, please be very clear and concise.

      Vijaykumar Shetye, Goa, India

  48. Elsina Moes says:

    I have an excel with dates in Col A and Dates in Col B with a value in Col C

    8/4/2015 8/4/2015 3703
    8/5/2015 8/7/2015 3705
    8/6/2015 8/10/2015 3708
    8/7/2015 8/11/2015 3715
    8/8/2015 8/12/2015 3728
    8/9/2015 8/13/2015 3731

    I would like to move the dates in Col B along with the value (which represents how many students made enquiries for our programs on that day) in Col C to line up with the date in Col A. Is there a formula for such an endeavor

    • Vijaykumar Shetye says:

      Assuming that your data is in cells A1 to C6,
      Paste the following formula in cell A7
      =TEXT(B1,"dd/mm/yyyy")&" "&C1

      The displayed result will be
      08/04/2015 3703

      Is this what you want to do?

      Vijaykumar Shetye,
      Goa, India

  49. David says:

    Hello,
    I have a spread sheet with a Header in D1 (Issue date). I am trying to get result in Column F (Review) of "Not Due" if the date is less than 640 days from issue, "Due" if the date is between 641 to 720 days from issue, and finally "Over Due" if it is greater than 721 days from issue. I have been trying the IF function and can only seem to get 2 returns but not the third. Thanking you in advance.
    David.

    • David says:

      I think I've got it;
      =IF(OR(D2=""),"",IF(D2>=TODAY()-638,"Not Due",IF(D2>=TODAY()-731,"Needs Review","Over Due")))
      I have then applied Conditional formatting so that Not Due = Green, Needs Review = Yellow, and Over Due = Red
      Seems to work Ok.

      • Vijaykumar Shetye says:

        The formula will work correctly, but you may include a few changes in the same.

        (1) The 'OR' function which you have used, is meant for checking whether 2 or more arguments are True. In your case, there is only 1 argument which 'OR' is checking. Hence it serves o purpose.

        (2) Today()-638 or (any date) minus (any number) could possibly give us negative values, if the number being subtracted is sufficiently large. The possibility could be avoided by adding 638 to D2, instead of subtracting it from Today().

        I have edited your formula as below.

        =IF(D2="","",IF(D2+638>=TODAY(),"Not Due",IF(D2+731>=TODAY(),"Needs Review","Over Due")))

        Vijaykumar Shetye,
        Goa, India

  50. Matthew B says:

    I would like to know how to set a field to give me the next day after TODAYS date that is a certain day i.e, i want the next Thursday after today, or the next monday. etc. that auto updates when i open the spreadsheet.

    • Vijaykumar Shetye says:

      FORMULA 1
      =IF((7-WEEKDAY(A1,14)+1)=7,A1,A1+1+7-WEEKDAY(A1,14))
      Shows the date of the current Thursday, till the end of the Thursday, and the next Thursday, after the end of Thursday.
      Reference of date is in cell A1.

      To change the day of week from Thursday to any other day, change the value 14 in the cell to
      11 for Monday, 12 for Tuesday, ... 17 for Sunday.

      FORMULA 2
      =IF((7-WEEKDAY(TODAY(),14)+1)=7,TODAY(),TODAY()+1+7-WEEKDAY(TODAY(),14))
      Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
      Automatically calculates for Today.

      FORMULA 3
      =IF((7-WEEKDAY(A1,1&$G$1)+1)=7,A1,A1+1+7-WEEKDAY(A1,1&$G$1))
      Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
      Automatically calculates for Today.
      Weekday to be entered in cell G1 as follows,
      1 for Monday, 2 for Tuesday,... 7 for Sunday.

      Kindly change the cell references in the above formulas as required.

      Vijaykumar Shetye,
      Goa, India

  51. Lianro says:

    Please help me find a formula to calculate the date it will be in 60 days (with custom holiday dates removed). I've been stumped with this. Thanks in advance!

  52. Chris G says:

    I am trying to return the number of days from a set of dates (past and future) and todays date but display the resulting number of days prefixed with + (future) or - (past. Thanks.

  53. Vijaykumar Shetye says:

    If you want to eliminate the negative sign, then use the function
    ABS (absolute) with your formula.
    Example
    =ABS(TODAY()-A1) or
    =ABS(A1-TODAY())

    Vijaykumar Shetye,
    Goa, India

  54. Lester says:

    Hi,

    I have a monthly budget, and would like the due date to change automatically. For example, if payment is due on 5/3/2016, the following day, I want the date to change to 6/3/2016 automatically. Can you help?

    • Vijaykumar Shetye says:

      Dear Lester,

      Use the below formula
      =MAX(A1,TODAY())

      It will display the due date (5/3/2016), which is entered in cell A1, till the current date (in your case 5/3/16).
      After that, it will start displaying the current date (6/3/16 onwards), every time the file calculates.

      Vijaykumar Shetye, Goa, India

  55. Yogesh Zagade says:

    Hi,

    M having attendance data like check IN time in "A1" column Date is 16-Apr-16 and "B2" column Time is "17:55:00" and check Out date in "C1" column is "17-04-2016" and "D1" column Time is 00:53:00. Please let me know what is total duration of working hours. We require like HH:MM format.

    • Vijaykumar Shetye says:

      Dear Yogesh Zagade,

      Use the below formula, and format the cell in whichever format you require.
      =D1-B2+C1-A1

      Vijaykumar Shetye,Goa, India

  56. Michelle says:

    I am creating a protected 60-day calendar. Cell E2 is unlocked for a date input. The calendar week starts on Monday, going through Sunday (A7-G7). The calendar grid is A8-G8 all the way down to A17-G17. I want to be able to input a date in E2, (ie:5/10/16) and for excel to know that 5/10/16 is a Tuesday, so it automatically inserts 5/10 in the cell B8. I can then formulate for the autofil of the rest of the dates all the way through the end of the 60 days.
    I just cant remember how I set this up before, where excel knew which day (monday-sunday) to start the calendar by entering a date in E2...

  57. Vanessa B Soto says:

    Hi!

    I'm using the =EDATE(A4,6) function. However, in my A4 cell, there is not yet a date but the function returns 182, which is 6 months from 0 (nothing in the cell). How do I get the cell that has the EDATE function to show nothing until a date is input into A4?

    Thank you!

  58. Dan says:

    Hi-
    What formula could I use to determine if a 'milestone' anniversary date (5,10,15,20 years, etc.) is reached within a quarter?
    So say my anniversary date is 4/6/1980 and I want to know if a milestone was reached between during the 3rd quarter current year (July 1 & Sept 30, 2015) - how can I calculate that?
    Thank you!

    • Vijaykumar Shetye says:

      Dear Dan,
      Use the below formula.
      =IF(AND(MOD(ABS(YEAR(TODAY())-YEAR(A1)),5)=0,ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0)),"Milestone Quarter","-")

      For every milestone quarter, it will display "Milestone Quarter".

      Vijaykumar Shetye, Goa, India

  59. Leigh says:

    I am trying to create a worksheet that will provide the month of a first shipment based upon the day of the month that a product was ordered. i.e if prior to the 5th of the month the first shipment will fall within that month. After the 5th, the first shipment will be sent the following month. Additionally, I need to determine the monthly shipping schedule based on the first shipment and the product frequency purchased. The frequency options are monthly, quarterly or bi-monthly (every other month).

    Can this be done with a series of date functions?

    Thank you so much for your help!

  60. Michael Nosek says:

    Hello Svetlana,

    I'm creating spread sheet where I would like to for eg. in B3 cell place a date of the project to start then in another cells will automatically change to name of the month, another will change to "day number of the month", 1, 2, 3 and so on, and another cell below will change to name of that day but single letter only (instead of Monday just M, T, W, and so on)

    Will you be able to help me achieve my idea?

    Best regards
    Michael Nosek

    • Vijaykumar Shetye says:

      Michael Nosek

      (1) For display of the month number, change the format of the cell.
      Go to
      Home - Format Cells - Number - Custom - Type
      and enter the letter m
      The date value in the cell will not change, but the month number will be displayed.

      (2) For display of the fist letter of the weekday, use the formula.
      =CHOOSE(WEEKDAY(A2,1),"S","M","T","W","T","F","S")

      Regards,

      Vijaykumar Shetye, Goa, India

  61. Ravi says:

    1.First thing I need how to show like this "21-jun-16 to 24-jun-16" in a single cell in excel.

    2.Second Thing how I need to calculate the values of between the dates of 21-jun16 to 24- jun-16 which is in another work sheet.

    • Vijaykumar Shetye says:

      Dear Ravi,

      1. To display "21-jun-16 to 24-jun-16", type it out in a cell and click enter.
      I don't think you have expressed your query properly.

      2. To calculate values between dates, use the below formula.
      =Sheet2!A1-Sheet2!B1+1
      Change the cell references as required.
      I have used Cells A1 and B1 of Sheet2 for the dates.

      Vijaykumar Shetye, Goa, India

  62. Deepika says:

    My question is i have set of rows having date timestamps across 2013 to 2016. as 01-mar-2014 00:07, 04-Jul-2015 06:40 and so on.. My requirement is I need to extract only Month and Year alone so as to do a pivot on it. It can be done using text as text(A10,"mmm-yyyy"), but a pivot on this data will return the text values not sorted one as Jan-2013,Feb-2013 ... May-2016. If i use date value and then do a pivot on this column, it gives me only list of month not month-year as Jan,Feb,Mar and so on.
    Please help.

    • Deepika says:

      Let me be clear with the requirement. I need pivot on date field . it should display the values as below
      Jan-2013 30
      Feb-2013 32
      Mar-2013 40
      ...
      Jan-2016 30
      Feb-2016 10
      Mar-2016 02

      Instead of displaying as below
      Jan 60
      Feb 42
      Mar 42

      I dont want to use group function in pivot (Group by Moth,year).Coz i have to do various calculation based on this value.
      Hope you got my requirement.

      • Deepika says:

        Dear Team,
        I got it. I applied same logic. Converted date to text as text(A10,"mmm-yyyy"). Then took date value. this time it returned with year. After which i applied pivot on it. Now am getting as
        Jan-2013 30
        .
        .
        .
        Mar-2016 02

        Many Thanks.

  63. Ramesh says:

    Hi i just want to know to summarize the value of activity performed as on date today in columns at the start of the table. how can we do ??

    • Ramesh says:

      I would like to prepare data like this with dates on the next columns

      District Total systems Total AMC completed till Yesterday Total AMC completed today 01 02 03 04 05....

  64. mukundharao says:

    Dear Madam / Sir

    Good Morning..

    How check Average In Caller ( It's A Grade / B Grade )

    Ex..

    Caller Name - XYZ
    Calles Made - 120
    Calls Connective - 70
    Appt - 10
    Turn up -5

    Please Explain Me How to Calculate Caller Average

  65. Muhammad Ali says:

    what i need to know is

    12 JULY 16 is todays date.
    18 JULY 16 is the date where i need the follow up.
    i subtracted 12JULY16 - 18JULY16 = 6

    NOW I NEED THIS NUMERIC 6 to get less day by day till the 18TH JULY 16 arrives

    any formula ?

  66. Peter says:

    if in cell B1 i have a date 3/23/16 and in cell B2 5/23/16 another date and in cell B3 4/23/16 another.i need to work out if the today's date is 6/23/16 being cell A1, then if cells B1,B2 & B3 is smaller than 30days then put it in cell D8 if they are 31days and less than 60 then put it into D7 and if it is 61 days and less than 90days then put it into D6.

  67. queenier says:

    hi can you help me?

    IF AG3 IS 2 DAYS BEFORE DELIVERY DATE THE RESULT IS "DELIVERY DATE" IF AFTER DELIVERY DATE THE RESULT IS "DONE" WHEN AG3 HAS NO DATE THE RESULT IS "NO SUPPLIER DELIVERY DATE" OTHERWISE "ON GOING

  68. Kris Patimonio says:

    How about when you are about to get the the formula on what day of the week will your 100th birthday fall?

  69. John-e-Action says:

    Hi i need a formula that will check the day is >14, the month is > 7, anfd the year = 16 inserted using the 'TODAY()' function. if the result =TRUE, then insert 500 otherwise enter ""

  70. Mansi says:

    Hie is there any formula to track the date occurring in next 2 months?

  71. peter says:

    Hello,

    I have two dates in two different cells (A1 = 4/12/1993 and B1 = 04/05/1993) and i want to verify if they fall with the same quarter (89 days). If two dates are within the 89 days, the data "passed" if outside of 89 days, it fails...

    thanks for your help....

  72. Jeff Nusser says:

    I want to count the number of cells that are before or after a specified date.

  73. Jams says:

    Hello, can anyone help me what is excel formula if the date will tell it is overdue in: equal or less than 3 months, greater than 3 months, greater than or equal to 6 months?

  74. LINDA OWEN says:

    I am making a trial calendar for my law firm. I need to calculate, for example, the date of trial -100 days. They need to be calendar days, not work days, and I have already set up a list of holidays for the next two years. I cannot figure out how to do the formula for the date -100 CALENDAR days, including holidays. Can someone please help me. I have been working on this for days. I have it totally figured out for the dates that need to be WORK DAY, but cannot figure out the ones for calendar days. Any help would be greatly appreciated. NOTE: I am working in Excel 2003.

  75. Cathy says:

    Hi Sir, I really need help on my excel. I downloaded an excel report from one of our tracker system tool. I sent it to my costumers and when they received it, the excel contains future dates. I cross checked my excel file but it has no future dates, I really don't know the issue here. Please help me :)

  76. adithya says:

    i am handling a tracker and i need the due date column as 20 months from current date.suggest me the formula

  77. nOOB ME says:

    hi,

    how can i make a format like this?

    departure , rejoining , "on leave, working"

    12 aug 16 , 2 sep 16 = the excel will only show that " on leave or working

    thank you.

  78. Attorney says:

    I am not sure if a question similar to this has been answered. I need to calculate dates pertaining to the service and filing of certain documents with a court.

    Here are the big factors:

    The document has to be filed with the Courthouse on the "Entry Day." The entry day is ALWAYS a Monday and MUST be a Monday.

    In order for the filing to be accepted by the Court, it has to have been served upon the other party between 7 and 30 days before the Entry Day. We can call this the "Service Period."

    So, using an Entry Day of 8/29/2016, the document would need to have been served on the other side sometime between 7/30/2016 and 8/22/2016.

    The "Trial Date" is always 10 days AFTER the Entry Day and is always a Thursday. Using this example, the Trial Date would be 9/8/2016.

    The other side must respond 7 days after the Entry Date. This is the Answer Date and is also always a Monday. Note: The Answer Date is also 3 days before the Trial Date.

    I have put together a formula that can calculate all the dates I need to know based on me inputting a possible Entry Date. It will then tell me the Service Period, the Trial Date, and the Answer Date. However, this requires me to first look up on a calendar the next few Mondays and then to, by trial and error, plug in potential Entry Dates to see if I am still in the Service Period window.

    What I would like to know is if it is possible to be able to plug in today's date and have all the potential dates calculated for me. However, the key is that the formula must always account for the fact that the Entry Date must always be on a Monday and that the Service Period must end 7 days before the Entry Date (also on a Monday), and that the end of the Service Period cannot be a date that has already passed. If the end of the service period has already passed, I would like it to move all the dates forward to the following week.

    For example, I would like that if I put in today's date of 8/18/2016, the formula would recognize that the next Monday (8/22/2016) is less than 7 days away (in this case, it's 4 days away) and therefore, Monday 8/22/2016 could not be a valid Entry Date as it would violate the Service Period requirement. Therefore, it would then automatically make Monday 8/29/2016 the Entry Date and base all other dates upon that date.

    Sorry if this is not clear.

    Thank you very much.

  79. Abdulrahman says:

    Hi,

    I have drop list (monthly, quarterly and annually) I want the end date of the contract automatically changed based on my selection of the drop list

    If I choose monthly, the end date will be 1 month after the effective date
    If I choose quarterly, the end date will be 3 months after the effective date

    and so on

  80. Ronald says:

    Hi,Svetlana Cheusheva
    what formula shall I apply to minus a fraction from a date? ie; 2/9/2016 - 1/3 or 2/9/2016 * 1/3 or 2/9/2016 + 1/3..
    what will be the next date if such is calculated?

    thanks

  81. Brian STRATFORD says:

    I have 2 columns target date e.g Dec-16 and Actual date e.g Dec- 16, what I need is to create RAG status in a 3rd column
    So if column g is equal to column h gives me An A
    if column g is less than column H gives me a G
    if column g is greater than column H gives me a R
    so if I could ask for assistance on a formula as going round in circles trying IF etc

  82. Beckie says:

    I have a spreadsheet that needs to have a date highlighted (column A) if it exceeds the 48 hour due date (column C -hidden and auto calculated) IF a completion date is not located in Column D. I would also need the highlight reverted once a completion date is added to column D. any thoughts? This spreadsheet has blank, formatted rows below for when additional items are added. Any ideas? Thank you!!

    • Beckie says:

      This is what I have currently, but nothing is changing, and many items should be highlighted RED now...
      ="IF($C>=TODAY),($D=ISBLANK)"

  83. ankit says:

    Hi,

    I want date of every Monday of every month when Monday came, date can be changed automatically. Please Help.

  84. Dwarkanath bari says:

    i want a formulation in excel

    design target design to costing send date costing target
    05-04-2016 07-04-2016 09-04-2016

    if first dept. not able to achieve there target date then next department will allocate delays no of days as per design to costing send date

  85. Phantipa says:

    I would like to know that can excel calculate and show the date of Friday before that day or not.
    For example,

    A B
    Customer required shipment date 11-Jan-2017
    Shipment Date that we need to ship 21-Nov-2016 (I use formula B1-51)

    However, The loading date of the shipment needs to be on Friday. So, we would like to know the
    formula that can calculate the Friday date before 21-Nov-2016 which is 18-Nov-2016.

    Please kindly advice whether it have the formula to calculate this kind of problem or not.

  86. Larry says:

    Need any help you can provide for following situation.

    I'm maintaining a yearly Excel spreadsheet for 1 Dec - 31 Oct to account for a variety of activities. Trying to provide Quarterly and Annual summaries has required yearly change of 95 comparison functions since I don't understand how to compare 1-Dec-2015 through 28-Feb-2016 without first determining the data equivalent of each date, e.g., 42339 and 42428 respectively. Solution has been (for 1st Quarter):
    =SUMIFS(Contacts!$K$3:$K$5002,Contacts!$D$3:$D$5002,">=42339",Contacts!$D$3:$D$5002,"<=42428")

    How can I just compare each transaction with a 1-Dec-2015 and a 28-Feb-2016? Or more sustainable comparison. And, once accomplished, annual update of spreadsheet would allow blanket change of applicable respective year(s) instead of having to change all 95 individually. Would greatly appreciate your assistance.

    Larry

  87. linsey says:

    Hello,

    I have tried using the datedif function in many different ways to calculate the total months difference in between two cells values. It has to include the month it is in, and the month it is ending in (oct 2016). So for example, it shows Paid through, and always ends in Oct 2016. So this would be 9 months. Including Feb, to Oct. If anyone can help in regard to this it would be appreciated!! I keep getting #Name? and #Value errors.

    Paid Through October 2016 Total Months Difference
    1-Feb-16 1-Oct-16 #NAME?
    2017-03 2016-10 #VALUE!
    2017-02
    2016-10
    2016-10
    2016-09

  88. Jo says:

    PLEASE HELP!

    I'm trying new ways to use excel and I saw someone have a roster up that automatically changes the day in the column.

    How do I achieve this? Do I use a formula or macro?

    I'm so confused but amazed at the same time.

    Any help would be greatly appreciated :)

  89. Prashanth Krishna says:

    Name ID Date
    Sagar 123 9/26/2016
    Sagar 123 9/27/2016
    Sagar 123 9/28/2016
    Raghu 234 9/26/2016
    Raghu 234 9/27/2016
    Raghu 234 9/28/2016

    In next column, I need the largest date to be displayed for every person. After displaying the date, 2 cells should be empty and again largest date for 2nd name. Again 2 cells will be empty and largest date for 3rd person. I need result looking like that. Is there any formula?

  90. Tomas says:

    Hi,

    I need to get the number of Days (day 2 - day 1 below), starting from today:

    day 2: the previos working day (yesterday, or if yesterday was not a working day, the last working day before that).

    day 1: the working day before day 2.

    If possible with regards to hollidays (which I assum must be in a separate list)

    Thnks all, Tomas

  91. Lana says:

    Hi,

    I am working on a database using excel, and trying to get some conditional formatting up and going.

    Basically, I would like an entire row to be highlighted if one cell in that row (date format) returns a date within a certain year (or even within the past year).

    I've only just begun using conditional formatting, so apologies if this is not the place to ask!

  92. Elke Johnson says:

    I am trying to build a spreadsheet of folks showing EOD dates (Entry on Duty). What I would like the formula to do is change their available leave automatically once they reach their 3 or 15 years of service. In other words, before the 3 years you only accumulate 4 days of leave whereas after 3 years it will go up to 6 days, and 15 yrs. to 8 days.
    Is this option possible?
    Many thanks in advance.

  93. Wendy Harwood says:

    Please can you help, I need to set up a spread sheet and need to have a column that shows that days date when a Y is put into another cell, can this be done and if so what would the sum be.

    Many thanks

  94. Irina says:

    Hi,

    Could you please advise me.

    I have a staff hollidays sheet.
    First collumn with the staff name and first row with callendar dates.
    Each hollidays days is marked with a h, and with a countif formula is count the total.

    What i need is a formula that would work as if would be the countif formula, but counting until the date of today.

    Generally speaking would be something as, How many h do we have until the calendar date that we are on.

    Kind regards,
    Irina Lopes

  95. matt Teuteberg says:

    I am trying to make any dates before today's date change into 9/9/999. If the date is today or after, I want it to stay as is. I have been using this function and it does not work.

    =IF(U2<TODAY(), "9/9/9999", U2)

    Please help. And by the way, when I put data into these cells, it just deletes the formula. This issue seems like an Excel 101 issue I can't find a solution online for. Please help.

    • Emily says:

      Hi Matt,

      You cannot have a formula perform a function on itself (the same cell). You have to enter the formula in a different cell and it will perform the function you are attempting to do. So, say the dates you are entering are in column A. If you create column B next to it, and then have the formula you are using refer to the values in column A, you will get the result you are looking for.

      Example: Enter 1/15/1995 in cell A1. Then in cell B1 enter =IF(A1<TODAY(), "9/9/9999", A1) . Cell B1 will display the date if it is today or in the future, and will display 9/9/9999 if it is in the past.

  96. Hans says:

    Hi, I'm looking the formula for a "date and time of completion". For example.

    Start Date and time 13Nov2016 12:30

    Cargo onboard 2000 bbls
    Rate/hr : 50 bbls/hr

  97. Varinder MAhajan says:

    i am trying to calculate month and days difference in two days
    cell A1 07/07/2016
    cell b1 07/10/2016

    formula
    =ROUNDDOWN((B1-A1)/365,0)&" "&"Year"&" "&TEXT(B1-A1,"mm")&" "&"months"&" "&TEXT(B1-A1,"D")&" "&"Days"

    output 0 Year 04 months 1 Days
    answer should b 3 month why it is calculating wrong days and month

    plz reply

    if possible plz let me know can i use text function for year difference also?

  98. SHASHIKANT SAHU says:

    How to get current (day) in excel .

  99. Isabel says:

    Hi, I've to worksheet in the Excel, one that compiles all my information and another where I insert all the products received. The received products sheet contains all the info from the product received including the expiration date.
    I would like that the information for the expiration date will appear in the main sheet, so I used the VLOOKUP. The problem is that most of the times I receive the same product more than once so I will have different expiration dates. How can my main sheet only show the latest expiration date?
    Many thanks

  100. Chris says:

    hi. I'm trying to set up Conditional Formatting using a Funtion affecting 3 cells (A4, B4, C4) based on a cell's value (C4).
    I am trying to use today's date (<=TODAY()) and the presence of a date in this same cell (=NOTBLANK()).
    I'm trying to do this in single Function (AND).
    is this posssible?

  101. bandita says:

    1. I want repayment schedule for individual loan where we can give logic If my repaymnet date will be a holiday like Sunday or Saturday then the repayment date will be automatically come next day.

    2.I want another repayment schedule where we disburse loan to my client in two different tranche. suppose today I disbursed one tranche and another for partially utilize of loan amount.

    SO please share formats.

  102. Leesha says:

    How do I calculate the number of Friday's between a current date and a future date?

    Thank you,

    • Hello Leesha,

      You can use this formula:

      =INT((WEEKDAY(TODAY()-6)-TODAY()+A1)/7)

      In the formula, A1 is the future date, and 6 stands for Friday. If you want to count the number of some other day of the week between two dates, you can replace 6 with other numbers between 1 (Sunday) and 7 (Saturday).

  103. RAY says:

    Report Generate a serial number for events with dates
    Ask a question
    degree
    Hello, I have a problem of generating a serial number from an event on the spreadsheet that has date of event and time, amount. how do i get the formular to compute the the corresponding slip id how do you compute such assuming it is represented in the spreadsheet like this

    From: 14.11.16 00:00 To: 18.11.16 00:00

    SLIP ID REG DATE ADMIN STAKE TO REGISTER STATUS IS WON? P.R.
    ... 14.11.16 21:43 1000.0 YES Accepted WON 1408.75

  104. Josh Michael says:

    Hi,
    I need a formula for:
    Minimum "Purchase Only" between 1/1993 and 12/1993.
    Thank you.

  105. Baruna says:

    HOW DO I GET AMID DAYS OF START AND FINISH DATES CONSIDERING 1ST AND LAST SATURDAY AS WORKING.

  106. JANAK says:

    DEAR SIR/MA'AM,
    I WANT TO KNOW NUMBER OF MONTH BETWEEN TWO DATE IRRESPECTIVE OF COMPLETED MONTH.FOR EXAMPLE IF 04-APR-2016 TO 03-MAY-2016 THAN FOR WHICH FORMULA WE CAN USE TO GATE ANS 2MONTH

  107. Dawn Habhab says:

    Hi,

    I need a column with a date that a job position was opened, then the next column I need to have a running total number of days the position has been open that calculates daily...help :o)

    Thanks!

  108. Steve says:

    I have a worksheet I am building for a friend & they want to be able to enter an event start & end date for the event. Once these dates are entered, I need a formula that will determine if the event occurs during either of the two stormy periods where they live.

    I have been trying to just combine an if statement with an AND statement, which works just great, except I have to provide a year for the statement to work correctly. My problem is that I need the formula to work no matter what year the event occurs in. Is there a way to either get Excel to ignore the year or for me just to include a YEAR((today)) tag in my dates which delineate the stormy season?

    The formula I am using is as follows:

    =IF(AND(G28>=J25,G29<=K25),"Chance of Storms","Clear Weather")

    where
    G28 & G29 = the start & end dates for the event
    J25 & K25 = the start & end date of the normally stormy season (June 17th through July 29th).

  109. Jess says:

    Hi I am working on a "Charge, Credit, Balance worksheet"
    Example: I have a tenant that in a year period has only paid 8 rent payments out of 12.I want to apply the 8 payments to the oldest balance.

    Date charge Credit Balance
    January 500 600 1827
    Dec 500 0
    Nov 500 287
    Oct 500 0
    Sep 357 0
    Aug 357 0 0

    I want to get a break down as if I apply Nov Payment to August charge, what would I be charging for Augs(I am aware that 357-287=70) so 70 will be apply to sep's charge 357+70=427-January's payment of 600. 427-600=(173)
    the 173 will be apply to Otb's charge which will let a charge of 327 for Oct instead of the 500.so if I was to give a notice to pay, It would look as follow:

    January 500
    Dec 500
    Nov 500
    Oct 327

    Total to collect 1827.

    I do it by hand, but I would love to know the formula to save me some time.

    I don't know if I made sense but, if I did can you please help me? I will greatly appreciate it. Thank you in advance.

    Jess

  110. Amar says:

    Hello Svetlana Cheusheva,

    I need a help. Actually I am a admin of a bio-metric device. There are 3 shifts are going in my office.
    In time Out time
    1. 6AM- 2PM
    2. 2PM- 10PM
    3. 10PM- 6AM

    but the 3rd shift time intime 10PM is taking as
    In time Out Time
    day1 out time 10PM
    day2 in time 6AM

    How can I make it for same date 10PM as intime and 6AM as out time in excel?

  111. Bill says:

    Is there an Excel function that will roll out (down a column or across a row) all the dates -in order - of a given month and year?

  112. james says:

    ok I have a column of dates and would like to count or total by year only. Say there 1000 dates I would like to know how many time 2013 shows up, out of the 1000, and how many times 2014 does so I can make a table. the format is y/m/d in the columns. The range of years is 2004 to 2030.

  113. Basant Kalra says:

    Hello,

    Please suggest formula for the below mention point :
    date of Birth 3-Apr-59
    Date of 60 year 3-Apr-19
    if Day grater then 1 then date of Retirement is 30th day of 60year date otherwise it is last day of previous month of 60year date.

  114. Raviprasad P Rao says:

    Hi

    I would like to calculate difference between today() and the earlier date by giving condition like if status='Closed', then it should not calculate the difference, if status='Pending', then only it should display the date difference.

    regards,

    Raviprasad.

  115. Zap says:

    Hi,
    Anyone can help.
    Is there any way to calculate the nearest coupon date from today( today is included) in excel given the maturity date, coupon times per year.

  116. Yaseen says:

    Hi,
    Can you please help me in creating moving vertical line with respect to week/date.

  117. Olga says:

    Thank you, very helpful.

  118. freekshot says:

    I need help.
    im working with conditional formating. I have this condition; when it's true the cell goes red =BA5>=DATE(2017;1;31). Everything ok. But i have already typed this date (31.1.2017) in BA5 cell, couse it's the end of the course (for my record). I would like that excel is counting days from the date i set this condition till the date that is written in cell (in my case 31.1.2017). So, if today is 26.1.2017, excel is coloring cell BA5 green. And it does that for another 4 days. 5th day its 31.1.2017 and it colors cell red.
    I believe i have to include =today() function, but dont know how. I have MS Office Pro Plus 2013.

    Thank you for your help

  119. Rohan Singh says:

    0606
    0407
    0108
    0509
    0310
    1804
    2504
    0205
    0905
    1605
    2305
    3005
    1306
    2006
    2706
    1107
    1807
    2507
    0808
    1508
    2208
    2908
    1710
    3110
    Hi Svetlana, I have date format in Taxt (First tow Number are date and Last no are Months) as mentioned above and required date format as mentioned: 18-04-2017. Can you please help me with Formula to get this format please.

    • Hi Rohan,

      Assuming the source data is in column A, beginning with cell A2, you can use the following formula to convert the text strings to dates:
      =DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2))

      The DATE function displays dates in the default short date format set in your Excel. To change the date format the way you want, please use the Format Cells dialog as explained in this tutorial:
      https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/

      Or, you can nest the Date formula within the TEXT function and specify the desired format directly in the formula:
      =TEXT(DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2)), "dd-mm-yyyy")

      However, please keep in mind that TEXT always outputs a text string that only looks like a date.

  120. Bipin Bihari Pattasahani says:

    Can you please help with below points :

    I want to use Condition like if particular cell date is Today then result will be (this) if cell date is Tomorrow then Result will be (this) or else result will be (this).

    What will be the Formula for the same...

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard