Comments on: Excel date functions with formula examples

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

Comments page 12. Total comments: 595

  1. Thank you, very helpful.

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

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

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

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

    1. Suppose birthday in A1 cell
      Then use this formula

      =IF(DAY(A2)>1,DATE(YEAR(A2)+60,MONTH(A2)+1,1),EOMONTH(DATE(YEAR(A2)+60,MONTH(A2)-1,1),0))

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

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

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

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

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

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

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

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

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

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

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

    Thank you,

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

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

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

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

  20. How to get current (day) in excel .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. Hi,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  49. 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 ""

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

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