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.
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)
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()
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")
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.
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
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.
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
monthsargument 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
monthsargument 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
monthsargument 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_dateargument and 0 inmonths:=EOMONTH(TODAY(), 0)
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.
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.
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!
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.
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).
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
Hi,
I need a formula for:
Minimum "Purchase Only" between 1/1993 and 12/1993.
Thank you.
HOW DO I GET AMID DAYS OF START AND FINISH DATES CONSIDERING 1ST AND LAST SATURDAY AS WORKING.
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
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!
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).
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
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?
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?
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.
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.
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))
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.
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.
Hi,
Can you please help me in creating moving vertical line with respect to week/date.
Thank you, very helpful.
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
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.
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...
Column G is a due date with a conditional formatting =AND(H9"Due",G9<TODAY()), so that it is highlighted once it is due.
Column H is the status "Open", "Due", "Done". I want Column H to auto populate "Open" or "Due" based on the date in Column G.
Help
Hey, Ellen,
if the date is situated in G1, use the following formula for column H:
=IF($G1<TODAY(),"DUE","OPEN")
If this won't help, please, specify the dates in your G column.
Hi Team,
Please help me the below query.
1. A column I have date like 01/01/2017 and B column I have date as a 01/15/2017. with same month date I have in A, B column it needs be red color(within Month). or else example like A column I have 01/01/2017 and B column have 01/02/2017 it needs to be yellow color .
Please help on this thanks in advance.
Regards,
Reddeppa.
The article you need is about formatting rules for dates. This part of it, to be more precise. There you will find detailed instructions on how to format cells that contain the same month.
MY INVESTMENT DATE IS 25/05/2016 AND ITS MATURE AFTER 779 DAYS HOW CAN I FIND EXACT DATE WITH EXCEL
To solve the task, you need to know how to add days to a date in Excel. So you can use this formula:
=DATE(2016,5,25)+779
or if the date is in A1 (5/25/2016):
=A1+779
Hi,
Please let us know how can freeze the today formula in excel 2007
Hi MANPREET,
The only way that I know involves creating a circular reference, which is generally not recommended. The formula is described here:
How to insert today date & current time as unchangeable time stamp
However, I strongly recommend that you read about possible side-effects and reasons not to use circular references in Excel before using that formula in your worksheets.
Hiii..
I have list of 200 cases with their details and next dates. i want only those cases to be displayed which are on tomorrow only. i dont want other cases to be shown with their details. if there are only 2 cases on tomorrow then it will show me only those 2 cases which are on tomorrow. i manualy inputs the dates.
Hi,
I have a list of duplicated project names with different timeframes, which I want to reduce to see the actual timeframe/phase - the one which MATCH TODAY's date best.
Due to this I removed the duplicates and now I need a formula that can choose the timeframe which MATCH TODAY's date best.
To make it more clear I already created a formula for the finish date, that finds the latest/maximum date, but not the BEST MATCHING TIMEFRAME TO TODAY's DATE...
Example:
Row A = Project names to be searched in(Duplicates incl.)
Row B = Project finish date
Row C = Project names (Duplicates removed)
MAX(IF(($A$1:$A$100=C1),(B2:B100)))
- It is an array formula, so press Ctrl, Shift + ENTER
Hope my question makes sense.
Best regards,
Niklas
Dear Sir/Madam,
My start run 01-jan-2016 that time km was 2000 km average per day is (15 km)
My question is at what time i will covered my km 10000 and in which date.
With Regards,
Kapil Sharma
7290849754
Hi,
I have a problem in calculating the EOSB using IF funct.
We have system which generates the calculation for us but I want excel function to help double checking my results.
The rules are as follows:
5 years gets full salary
thanks
Hi,
I have a problem in calculating the EOSB using IF funct.
We have system which generates the calculation for us but I want excel function to help double checking my results.
The rules are as follows:
5 years gets full salary
thanks
5 years gets full salary
what would be the formula for the following condition:
interest rate would be 15% for date jan 2014 to Dec 2014 & 18% after that
what would be the formula for following condition:
interest would be 15% for dates jan to dec 2015 and 18% for jan 16 onwards
Hi I'm making a template to keep a timetable and I would like formula to have the correct day of the week appear in cell B when the date is entered in cell A please.
I have a report that shows each month separate that throughout the month needs to have the current date, but the date needs to stop at the end of the month. (ex: current date is 4-10, on 5-3 the date needs to stop at 4-30 in that column) Is there an "end of month" stop date formula?
I am looking to have the date change to the next week after a payment is made.
Ex: payment is due on 5th. On the 12th, the next payment is due. Once the payment is put in, I want the date to change to the next week.
Is that possible?
1st May To 5th May
How can I show this using excel formulas.
This will continue for whole month like below
1st May to 25th May
1st May to 26th may
Hi,
I am looking for a formula for crew joining vessel.
Example:
Crew joining date on 1/4/2017
I wouldn't know the crew signing off date until given and at the same time I want to know how many days onboard as I need to monitor it on daily basis how many days the crew is onboard until his actual signing off date.
I would like to have Cell A (indicate Joining Date), Cell B (indicate his Signing Off date, which will be leave blank until date is confirmed), Cell C (indicate the number of days onboard as to date)
Thank you.
Allocation Start Date Allocation End Date
1-Oct-16 31-Mar-18
Hi,
I want May month networking days from the above mentioned allocation start date to allocation end date. please help me, i need to get the networking days for entire column.
Thanks
Hello,
I'm looking for a formula to calculate status in milestone table. In the table, there are lists of activities (rows) and 5 Milestones (columns across, M1-M5). I want the table to show the next milestone date to determine the status of each activity. Can you show me how?
thanks in advance,
Celeste
Would like to have day-of-week display as full text.
Cell A1 has date. Cell B1 contains =DAY() which displays the numerical day of week. Would like to have Cell B1 display the text version of the day, eg. Monday, Tuesday, etc.
Looking for a function where for eg you type in a statement date (30/06/2017) it will pick up everything in the other worksheets pertaining to that particular date/month - what formula do I use...that's if I am making any sense:)
Hello, Mel,
Well, this is an interesting questions, since we don't know how your lookup data is stored and whether you want to pick up a row/a column or something other than that.
For now, I can advise you to take a look at a few functions that may help:
VLOOKUP
HLOOKUP
IF
INDEX/MATCH
Or, a simpler method is to try our Merge Tables Wizard that can do that for you :)
I will enter data in every 10 days.I want to set upcoming specified date after no of day.if i enter data every day then this formula can be work=TODAY()+B1 where B1 is Project duration.
For an example:- Today date is 04/07/2017, data enter date also same 04/07/2017.
date Project duration in day
28/06/2017 17
30/06/2017 5
01/07/2017 3
04/07/2017 2
Hi,
What formula would I use if I want the excel sheet to count a drop down menu item for the current week.
Like lets say this is my excel sheet:
1 A B C ..K
2 7/17/17 Requests Can you fix my chair? Maintenance
3 7/18/17 Maintenance Fixed chair Low
4 7/21/17 Low Mopped Floor High
5 7/21/17 Requests Can I get my box fixed? Med
6 7/23/17 High Put Insulation in Requests
I want the excel sheet to count every time the sheet says requests and it occurred this week. I want it to update after every seven days.
I tried
COUNTIF(TODAY()+7, $K6)
$K6= Requests (data validated for drop down menu items for column B:B)
But it didn't work
Thanks,
Leilani
Hi
I am looking for a formula to inform me what is the excact day every 25th of the month every time i open the worksheet.
Thank you in advance
Alexander Michael Gkourtziev
Hi
i imported webdata and made a template in excel, but i have an issue i want it to refresh only in fist day of the month not only that i want it to store the average of each day information in a new sheet.
Thank you in advance
Alexander Michael Gkourtziev
Hello - I am trying to have one column populate a date, if another cell has 100% in it. Can you help me with this, please? An example of this would be, I need 30 of this particular item and I finally get my 30, I have a column that populates 100%, but I would also like a column to then populate the date I hit 100%. Make sense?
Hello Everyone...
Please some one can help me on this issue...
I have total days which has been calculated due dates between start date To end date..
now i want to know the formula for those days can be convert into datewise..
example - the start date is 01-Sep-2017 to end date 30-Sep-2017 so total days are here 30 days.
now i want the date format to appear in excel cell as last day of (30) day..
Can you use the weeknum formula with the =today() formula so that every time you open the sheet it updates the date and automatically gives you the current week number?
I want to enter a formula that debits an amount based on date.
Cell A1 = todays date. Other cell rows have transactions posted which i do want to happen unless it is at or past cell A1. Thank you
Hello, Marc,
could you please clarify what values you have in other cells? Are those some dates that you want to compare to A1?
Hi. I need to a formula for one cell in a column that will return the date for every Sunday from here on out. The date format i would like to use is 17-09-03. I would drag the cell containing this formula down the column to fill in the blanks on the spreadsheet. It will fill in this coming Sunday as 17-09-10 then continue into next year depending on how far I drag the cell each time. Right now I just manually enter the dates in the format mentioned. Any ideas please? Thanks
Hey there,
I am trying to figure out a formula in which I have a date in one column and the column next to it has to show a specific date for example A1 has a date ex today's date and B1 needs to show a date of 9/10/2018
I have a column of expiry dates. I need to display those dates that are going to expiry in the next two to three weeks.
thanks in advance.
Hello, Santa,
you need to use Conditional formatting here.
If the dates are in column A, you create a rule that will be applied to A:A with the following condition:
=(A1+14)>TODAY()
Please use the link above to learn how to create and use conditional formatting in Excel.
Dear Sir/Mam
Mail Excel Me Ak Aisa Farmula Chahta Hun Ki Agar Meri Vehicle Ka Time 50 Hr Fix Hai 2500 Km Ke Liye To Agar Wo Vehicle Late Ho Rahi Hai To Wo Red Colore Ho Jaye Agar Wo Vehicle Time Pe Pahuchi Hai To Green Agar Wo Time Pe Pahuchi Hai To Yelow Ho Jaye Agar Kisi Ke Pas Aisa Farmula Hai To Please Let Me Us Now In Urgent.
Thank's
Prabhat
Hi
Would you advice me how to make a cell not to display any result if calculating a date (date of birth).
What is actually happening is...I'm calculating a date of birth...but when I delete that date it automatically display it's own value e.g 117...the formula I've used is =dateif
Thanx
Jerry
Hi,
Can anybody help me in this?
I have the list of date, I need take out Previous date and Next date based on current data and it should update automatically whenever I open the excel.
Regards,
Ajit kumar
Hi There! Can you help me with this calculating the days from dates please? I want to add say 3/16/2017+3/20/2017 and see the result at 4.
Thanks for your help!
Cory
Hi, Cory,
if A1 contains 3/16/2017 and A2 - 3/20/2017, the following formula will return 4:
=$A$2-$A$1
Or you can use the other one:
=DATEVALUE("3/20/2017") - DATEVALUE("3/16/2017")
Hope this helps!
Can any one help me to create formula in Excel sheet for calculating tax 5% on or before 14/9/2016 and 6% from 15/9/2016 with the help of IF Statement.
_________________________________________________
A | B | C | D |
_________|____________|_____________|____________|
Date Amount Tax @5% Tax @6%
5/9/2016 5000 ? Formula ? Formula
14/9/2016 25000
15/9/2016 27400
25/9/2016 125000
5/7/2016 215600
5/4/2016 15000
25/10/2017 2000
Hello, Nitin,
If I understand your task correctly, please put the formula below to cell C2:
=IF(A2<="14/9/2016", B2*5%,"")
and the next one to D2:
=IF(A2>"14/9/2016", B2*6%,"")
you need to copy both formulas down the columns. If you don't know how to do that, please check out this article.
Hope it will help!
To Natalia
thanks you have solved my big problem.
You're welcome!
I am creating new dates based on a forecast date: Example a forecast date of 12/01/2017... to get me new date I use =EOMONTH(H17,0)+30, which this would create 1/30/2018. But that is a problem because I cant have dates go into the next year. It would have to stop at 12/31/17... how can I do that in excel?
Hi Team,
I would like to know , how to populate dates in this format, for example 08/01/2017 - 08/07/2017 and if I drag it i should get the next week's date like 08/08/2017 - 08/14/2017. Any help ??
Thanks & Regards,
Vam...
I want to create conditional formatting in one cell based on the date input of another cell. However, I do not know the value of the date. Is there a formula that says "whatever the date value entered in the cell + 1" for example? I am trying to calculate when something goes past due, but I do have an assigned date yet.
Thanks in advance
I meant that I DON'T have an assigned date yet.
my scenario is I have to calculate the first 6 months and extract the data ,then compare the column of which determines the period where one year is equivalent to R20 and the maximum is 5 years, after the period of which determined by the amount paid .then after the expirity theres 3 months period again then data is extracted t another table please help me out I really need help
,
Hello, sfiso,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
I have a problem
In sheet1 i have a table which is
A B C D E F
_____________________________________________________
1 Date Qnty
2 29/07/2017 130
3 07/08/2017 300
4 07/08/2017 220
5 10/08/2017 140
6 21/08/2017 50
7 07/09/2017 100
I want to put data which is on 06/08/2017 i.e data required nearer to 06/08/2017 (it's 07/08/2017)in cell E2
How can i do
please guide
Hello, dibya,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Hi,
I would like to change a date automatically to today's date once data is changed in another column.
e.g Column A is a Serial Number, Column B is the software Version, Column C is whether Serial Number has updated Software Version = 0 for no and 1 for yes. Column D is the date the software was updated. I would like to know what formula to use when we change the software version ... column D should automatically update to today's date.
If this is unclear, i will send the spreadsheet for clarity.
Hello, Maryka,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
I want to know how I can use the Duration Days and Start Date to get the End Date.
Example
Duration Days Start Date End Date
24 11/30/17
Hello,
Please try the following formula:
=TEXT(DATEVALUE("11/30/17")+24,"mm/dd/yy")
Hope it will help you.
I have a spreadsheet that I sometimes keep open for several days. I have the TODAY() function in one cell. There are many other cells that refer to this for date calculations.
Without saving/reopening, is there a way to trigger an update automatically after midnight? Executing the cell the next day doesn't update.
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
please help me
i want to calculate number list in one number.
i want like this
{1+5+8+16+7+26=63>>> but i want 6+3=='9'}
{19+15+32+16+167+366=615>>> but i want 6+1+5=='3'}
{451+456+268+176+9+256=1616>>> but i want 1+6+1+6=='5'}
i want '9','3'&'5' number count formula in excel.
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Dear Sudesh,
Use the formula
=SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(A2,COLUMN(A1:J1),1),0)),0)),COLUMN(A1:J1),1),0)),0))
Formula will provide the sum of the digits like 123456 as 3.
If you also want to get the first stage of the sum of digits, then you can use the following formula.
=SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(A2,COLUMN(A1:J1),1),0)),0))
This will give result of 123456 as 21.
Note:
1. Change the cell references as required.
2. Presently number is in cell A2.
3. Formula is to be entered as Array Formula, using Control+Shift+Enter, instead of Enter.
Regards,
Vijaykumar Shetye,
Panaji, Goa, India
Hi All,
Please help me for this. I have 3 different wordings in one column like TYPED, SEND & RECEIVED. Now I need to put date on next column like when I typed "TYPED" word on that column then in next column it need to show the current date, but wont change in next day when i open the excell on next day.
Like this when I put next word (i.e) "SEND" the current date has to be displayed and also wont change in next day when I open excell in next day.
If anyone can help me in this please help me.
Thanks,
Faizal
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
HI
want update every day automatically pending days (E.g one project started date available project not completed i want to know no of day's pending without inserting end date)
Hi Svetlana
If I have date in one cell, how can I have day in next cell which is corresponding with this date
I looking for 2 separate date formulas but ultimately produce the same information
1 formula I need it to return the number of working days in the month on that day without weekends or holidays included ex: 01/15/2018 = day 10. This will refresh at the beginning of every month
2 same formula but rolling for the whole year. excluding weekends and holidays.
Thanks
one date in a column on 20 rows. how it show in next sheet only in one row only one date?
I am building a sheet where i import data from a daily updated workbook. I put in a formula to calculate the date using =today(). However, I am finding it is changing the other dates in the column to the current date. Is there some other function i can do so it will not change the previous entered dates?
hi,
how can i add 90 days (3 months) and 365 days (1 year) in current using date
example
23/02/2018 to 22/04/2018
23/02/2018 to 22/02/2019
????????
Everytime I type in a specific column, I'd like the current date to show up in a specified cell
HI,
example
11/12/2017 - 5/3/2018 HOW MANY DAYS BALANCE COMING
Hi,
If your task is to calculate the number of days between two dates, I'd recommend you to try out our Date & Time Wizard. To see how the add-in works, you can download and install the fully functional 14-day trial version of Ultimate Suite that contains all our add-ins for Excel (60+). After the installation, you'll find Date & Time Wizard under the Ablebits Tools tab.
If your task is different, then please describe it in more detail.
when in cell a1 have only year and b1 have complete date, how can we subtract them considering the a1 as 01/01/####
what is wrong in this formula ?
plz correct it
COUNTIFS(DATE(YEAR(Y:Y,$AU$7,F:F,AS8)
while
Y:Y is a range of different dates
$AU$7 is a specific year
F:F is a range of different designations
AS8 is a specific designation
hi since MS in their infinite wisdom decided to remove date picker in office 365 I need answer to cell to have current date in cell but also once saved remain that date and not update to current date when file opened
TIA
Lewis
If I need to consider only month Feb 18 against 18-Feb-2018 which formula need to used ?
Hi,
I have a dated if statement but only want the true,false value to be considered in the future?
=IF(TODAY+$I$2()TODAY(),A1<=(TODAY()+days))
Hi,
I need an excel formula to calculate the number of hours that occurs between start and end of a given period, number of hours before and number of hours after that period.
Example: The period Starts 08 AM and ends at 04 PM
regards,
Francisco
Good day
I would like to hard code the 26th of March (irrespective of the year) as the start of week 1.
Date format currently: YYYY/MM/DD
How do I go about this?
Thank you!
what does mean this formula ?
=w(B15,B16,0)
what does mean =W ?
Thanks
Tarek:
I would say the "W" is a typo. I'm not aware of an Excel function "W". Either that or maybe it is a user defined macro.
Am puting date on letter 13 May 2018.and i want on other page date is automatically added by 2 days i.e. 15 May 2018 base on date i put on letter.
I am trying to get an IF formula to recognize a date entry so if the cell G9 contains a date, then return the value in cell F6 e.g. If(G9="date",$F$6) - however my formula is returning a result of FALSE even though there is a date in G9 which is 11/07/2016 in the format of dd/mm/yyyy. Any help appreciated, I have tried everything instead of "date" in the formula, I have used "dd/mm/yyyy" "number" "datevalue" but nothing is returning back the value in F6, I'm either getting FALSE or errors of #NAME? or #VALUE?
Dear MC,
Change the formula to
=If(G9>10000,$F$6) or
=If(isnumber(G9),$f$6,"False")
In Excel date is a number.
1 Jan 1900 = 1;
2 Jan 1900 = 2 and so on.
11 July 2016 = 42562, regardless of which number format you use.
I hope this solves your problem.
Regards,
Vijaykumar Shetye,
Panaji, Goa, India
Hello,
I have two sheets in excel 2007, where I enter via barcode scanner serial numbers of devices, one sheet is for direct sales and the other sheet is for credit sales.
I need a way to validate for duplicate s/n between two sheets. At this moment I am using the formula =COUNTIF(sheet1!d5:sj30000), like conditional formatting highlighting the duplicated cell. The system find duplicates very good, but becomes very, very slow.
Now I want to validate at specific time, for example in the night, when the system is not used.
Can you help me to setup time driven validation formula to trigger at specific time.
Thank you very much for your time.
How to get required data in pivot without filter option instead of using condition within pivot?
If I will update the data further in the table, the pivot will show the data with accept the condition.
HOW TO REFERENCE CELL TO AUTOMATICALLY CREATED DATE AND TIME THIS NOT CHANGE TODAY DATE ONLY CELL ENTRY DATE AND TIME.
DATE 01.01.18 AFTER 30 DAYS DATE WE NEED AND DATE IS THIS FORMAT ONLY ( EX:31.01.18 ). PLEASE SEND THE EXCEL FORMULA
Hello,
I am trying to modify a spreadsheet to highlight the dates I have to make a status call. I work at a financial firm, every time we process transfer paperwork, we have to call the contra firm 8 business days after mailing it, then every 5 business days after that. I would like excel to not only automatically generate these dates in at least three different columns, but I want it to automatically highlight the dates once they've approached. Are you able to provide a formula for this?
Hi,
Can i get an exact date from this only "Sept-2018"
I am creating a table where I just need to replace the specified cells details then change the date on one column.
Thanks ahead!
I think not possible
I have a column that calculates a projected date based on another column that explains the number of days that either needs to be added or subtracted and then posted as a projected and specific date so we can post to a calendar as an alert. However, what I REALLY need is a formula that shows the previous FRIDAY if or when it falls on a weekend or holiday.
HELP and THANKS!
-steve
Hello,
I kind of need some help. I have make a table containing dates which are deadlines. A side there is also a final deadline for that entire table. What I want to do, if any of the date changes the final deadline date should automatically change for the number of days which changed on that one date. Is it possible to accomplish that in excel and if it is, how can I do it? :)
Thank you in advance. :)
How to mention a date in salary sleep, if employee is absent on specific date?
Ineed the persian date(1397)in excel i tried alot but exel only has two date ie gregorean and hijri qamari date.the microsoft must instaled the persian date too.so now how can i solve my problem.
Hello, please let me know if a return of "year.month.day" is possible. So the column A would be:
2018.01.01
2018.02.24
2018.03.13
Etc,
Thanks.
Hello!
If column A already contains dates, you can simply set this custom format for them: yyyy.mm.dd
For this, select the dates, press Ctrl+1, on the Number tab select Custom in the Category list, and type the above code in the Type box. For more information, please see How to create a custom date format in Excel.
Hi,
I want to validate a cell with current date. if it is less than current date then the color of the cell should red or if it is equal to current date then the color should be green.
Regards,
Bibhu
Hi Svetlana,
I am working on Task Manager for 2019 and facing issue while retrieving the weekend date for current week (whichever it may be) excluding holidays and weekends.
I am using below formula:
=WORKDAY.INTL($L$1,NETWORKDAYS.INTL($L$1,$R$1,1,$Y$4:$Y$14),1,$Y$4:$Y$14)
$L$1- current date
$R$1- weekend date
$Y$4:$Y$14 - holiday list
It is working for normal week but fails whenever there is hoilday.
Thanks in advance.