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
by Svetlana Cheusheva, updated on
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 15. Total comments: 595
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!
Hi Vanessa,
Just add an IF function that checks for blank cells, for example:
=IF(A4="", "", EDATE(A4,6)
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...
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.
Dear Yogesh Zagade,
Use the below formula, and format the cell in whichever format you require.
=D1-B2+C1-A1
Vijaykumar Shetye,Goa, India
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?
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
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
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.
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!
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.
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
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.
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.
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
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
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
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
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
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.
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
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
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
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")
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
Hello, Scott,
Уou need a VBA script for this. Sorry, we cannot help you with it.
20180502
20170101
20180301
20190802
20170901
20180601
20170201
20160601
how to convert in to date yyyy-mm-dd 16'oct convert in 10-2016
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
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
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.
Hello,
Looks like you need VBA for this. Sorry, we cannot help you with it.
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")
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?
Hi,
Could you please share the formula to calculate the Last Present Date.
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
I would like to know how we can retrieve the date for 3rd sat of every month for the year 2015
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.
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
Hello, Vivek,
Please try the following:
right-click on the cell -> Format Cells... -> Date -> pick the format you like.
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
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.....
Dear Mukundha,
It is difficult to provide a solution without seeing the data. Could you please send a test worksheet with some records you want to use for the calculation to support@ablebits.com and describe the expected result?
We'll do our best to assist you.
Ok Madam
hi
thanks!!!
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"))
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
Hello Manju,
Here you go:
=IF(A1<>"",TODAY()-A1,IF(B1<>"","Completed","Don't worry"))
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
I want excel to fill on completion date if I change status to complete. What formula should I use?
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..
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
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!
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
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
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,
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
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)
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.
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
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
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
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
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"
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,
Hi An,
You can use the DATEDIF function to calculate the difference between two dates in days, months or years.
If you are looking for something different, please specify exactly what results are expected in your example for employee 1 and employee 2.
Hi Meer,
You can use the NETWORKDAYS function, like =NETWORKDAYS(A2, B2), where A2 is the start date and B2 is the end date.
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
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
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?
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
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