When it comes to calculating dates in Excel, DATE is the most essential function to understand. As you probably know, Excel does not keep the year, month and day for a date, nor does it explicitly store weekday information in a cell. Instead, Microsoft Excel stores dates as serial numbers and this is the main source of confusion.

Not all Excel date functions can recognize dates entered as text values, therefore it's not recommended to supply dates directly in calculations. Instead, you should use the DATE function to get a serial number representing the date, the number that Excel understands and can operate on.

## Excel DATE function

The Excel DATE function returns the serial number that represents a certain date. It has the following arguments:

Where:

**Year** - represents the year of the date.

**Month** - an integer representing the month of the year, from 1 (January) to 12 (December).

**Day** - an integer corresponding to the day of the month, from 1 to 31.

The DATE function is available in all versions of Excel 365 - Excel 2007.

### DATE function - tips and notes

The DATE syntax looks crystal clear and straightforward on the surface. In practice, there may be some unobvious pitfalls that the below tips will help you avoid.

#### Year

Excel interprets the *year* argument according to the date system set up on your computer. By default, Microsoft Excel for Windows uses the 1900 system where January 1, 1900 is represented by the serial number 1. For more details, please see Excel date format.

- If the
*year*argument is between 1900 and 9999 inclusive, Excel uses exactly the value you supplied to create a date. For example, DATE(2015, 12, 31) returns December 31, 2015. - If the
*year*argument is between 0 and 1899 inclusive, Excel adds the specified number to 1900. For example, DATE(100, 12, 31) returns December 31, 2000 (1900 + 100). - If the
*year*argument is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.

Tip. To avoid confusion, always supply four-digit years. For example, if you input "01" or "1" in the year argument, your DATE formula will return the year of 1901.

#### Month

- If the
*month*argument is greater than 12, Excel adds that number to the first month in the specified year. For example, DATE(2015, 15, 5) returns the serial number representing March 1, 2016 (January 5, 2015 plus 15 months). - If the
*month*argument is less than 1 (zero or negative value), Excel subtracts the magnitude of that number of months, plus 1, from the first month in the specified year. For example, DATE(2015, -5, 1) returns the serial number representing July 1, 2014 (January 1, 2015 minus 6 months).

#### Day

As well as month, the *day* argument can be supplied as a positive and negative number, and Excel calculates its value based on the same principles as described above.

Tip. At first sight, supplying negative values in the month or day argument of the Excel DATE function may seem absurd, but in practice it may turn out quite useful, for example in the complex formula that converts a week number to a date.

## Excel DATE formula examples

Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.

### Example 1. DATE formula to return a serial number for a date

This is the most obvious use of the DATE function in Excel.

For example, to return a serial number corresponding to 20-May-2015, use this formula:

`=DATE(2015, 5, 20)`

Instead of specifying the values representing the year, month and day directly in a formula, you can have some or all arguments driven by of other Excel date functions. For instance, combine the YEAR and TODAY to get a serial number for the first day of the current year.

`=DATE(YEAR(TODAY()), 1, 1)`

And this formula outputs a serial number for the first day of the current month in the current year:

`=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)`

Tip. To display a date rather than a serial number, apply the desired Date format to the formula cell.

### Example 2. How to return a date based on values in other cells

The DATE function is very helpful for calculating dates where the year, month, and day values are stored in other cells.

For example, to find the serial number for the date, taking the values in cells A2, A3 and A4 as the year, month and day arguments, respectively, the formula is:

`=DATE(A2, A3, A4)`

### Example 3. DATE formula to convert a string or number to a date

Another scenario when the Excel DATE function proves useful is when the dates are stored in the format that Microsoft Excel does not recognize, for instance DDMMYYYY. In this case, you can use DATE in liaison with other functions to convert a date stored as a numeric string or number into a date:

`=DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))`

### Example 4. Adding and subtracting dates in Excel

As already mentioned, Microsoft Excel stores dates as serial numbers and operates on those numbers in formulas and calculations. That is why when you want to add or subtract some days to/from a given date, you need to convert that date to a serial number first by using the Excel DATE function. For example:

**Adding days**to a date:`=DATE(2015, 5, 20) + 15`

The formula adds 15 days to May 20, 2015 and returns June 4, 2015.**Subtracting days**from a date:`=DATE(2015, 5, 20) - 15`

The result of the above formula is May 5, 2015, which is May 20, 2015 minus 15 days.- To subtract a date from today's date:
`=TODAY()-DATE(2015,5,20)`

The formula calculates how many days are between the current date and some other date that you specify.

If you are adding or subtracting two dates that are stored in some cells, then the formula is as simple as =A1+B1 or A1-B1, respectively.

For more information, please see:

### Advanced Excel DATE formulas

And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:

## Using Excel DATE formulas in conditional formatting

In case you want not only to calculate but also highlight dates in your Excel worksheets, then create conditional formatting rules based on DATE formulas.

Supposing you have a list of dates in column A and you want to shade dates that occurred earlier than 1-May-2015 in orange and those that occur after 31-May-2015 in green.

The DATE formulas you want are as follows:

Orange: `=$A2<DATE(2015, 5, 1)`

- highlights dates less than 1-May-2015

Green: `=$A2>DATE(2015, 5, 31)`

- highlights dates greater than 31-May-2015

For the detailed steps and more formula examples, please see How to conditionally format dates in Excel.

## How to make date formulas in Excel with Date & Time Wizard

Though DATE is the main function to work with dates in Excel, a handful of other functions are available to tackle more specific tasks. You can find the links to in-depth tutorials at the end of this article.

Meanwhile, I'd like to present you our Date & Time Wizard - a quick and easy way to calculate dates in Excel. The beauty of this tool is that outputs the results as **formulas**, not values. Thus you have a kind of 'two birds, one stone' opportunity - get the result faster and learn Excel date functions along the way :)

The wizard can perform the following calculations:

**Add**years, months, weeks, days, hours, minutes and seconds to the specified date.**Subtract**years, months, weeks, days, hours, minutes and seconds from the specified date.- Calculate the
**difference**between two dates. - Get
**age**from the birthdate.

For example, here's how you can add 4 different units in cells B3:E3 to the date in A4. The formula in B4 is built in real-time as you change the conditions:

If you are curious to explore other capabilities of the wizard, feel free to download an evaluation version of the Ultimate Suite below which includes this as well as 60 more time saving add-ins for Excel.

I thank you for reading and hope to see you on our blog next week!

## Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)

## 676 comments

Hi

Relatively easy one (I hope!) - i just need to calculate the salary due to a leaver based on the days in the month.

If a person is leaving, they receive their salary / 12 / days in month of leaving * how many days up to their leave date. So someone leaving on 19th March would receive 30000 / 12 / 31 * 19 = 1532.26

We have the salary and date of leaving in separate columns, how do we put in a formula to do the 1532.26 using the salary and date of leaving columns?

Thanks for help! : )

Hi! To determine the current day of the month, use the DAY function. To determine the number of days in a month, use the EOMONTH function. Try a formula like this:

=30000/12/DAY(EOMONTH(TODAY(),0)) * DAY(TODAY())

For more information, see this guide: Get the last day of month (EOMONTH function).

Thanks for this and I can see the benefit and will use in the future.

But what if I wanted to calculate the salary due on the actual leave date as opposed to todays date:

30000 19/03/2023 Formula to return 1532.26

Many thanks

Don't worry, I've used the basis of your formula and inserted the column references and it now works fine! : )

Many thanks again

Hi! Instead of the TODAY() function, use the DATE function to determine the target date. You can find more information about this function in the article above.

Hi,

Is there a formula for turning dates and events as data in an excel spreadsheet into an excel calendar template.

Thanks for your help

Hi! I don't think it can be done with a formula. Here is some information about Excel calendars that might be helpful: How to create calendar in Excel.

Date 30-11-22

Need FY 2022-2023

What is the function with formula ??

I am not sure I fully understand what you mean.

Is there a formula that combines the vlookup function (for invoice due date terms) and due date based off invoice date? I have a formula that automatically adds 30 days to the invoice date to get the due date, but a handful of customers have different terms (45, 60 and 90 days) that I have in a separate table. I want to take the invoice date, use the vlookup function on the customer's name to get the terms and then get an invoice due date. Is this possible?

Hello!

If I understand your question correctly, you can use the VLOOKUP function to find payment terms by customer name. Then use these payment terms to determine the payment date. For more information, please visit: Excel VLOOKUP function tutorial with formula examples.

I hope I answered your question. If something is still unclear, please feel free to ask.

Is there a formula to extract the date from a cell containing "Wed Feb 22 2023 00:00:00 GMT-0500 (Eastern Standard Time)" and convert it to mm/dd/yyyy?

Thanks in advance!

Hi!

Вы можете тимпользовать функцию MID чтобы извлечь нужные части текствов

=DATE(MID(A1,12,4),MONTH(1&MID(A1,5,3)),MID(A1,9,2))

Hello!

You can use MID function to extract the desired parts of the text string and use them in DATE function.

=DATE(MID(A1,12,4),MONTH(1&MID(A1,5,3)),MID(A1,9,2))

To convert text to date, you can use this Excel tool. With Ablebits Text to Date tool, users can quickly convert dates, including those with different separators (dots, dashes, slashes, etc.), correctly interpret ambiguous dates or dates in various formats, and format resulting dates according to their needs. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Dear Alexander

I have a list of task with different dates.

I want to get e text "due in first week", due in 2nd week, due in 3rd week, depending upon the due date.

Would appreciate your input on this

Rgds

Louis

Hello!

The information you provided is not enough to recommend you a formula. The following tutorial should help: Calculating week number in Excel (WEEKNUM function).

=IF(TODAY()=DATEVALUE("2023-02-07"),INDEX(PLAYING!$E$1:$G$200,MATCH(DE4,PLAYING!$E$1:PLAYING!$E$200,0),2))

This formula works great except, once it executes on the given date it gives me the correct answer. But once the date has passed it changes the answer to FALSE. Some how I need to retain the answer that was given on the exact date of execution. I have been copy, paste, value to retain the correct answer. But was looking at anyway to keep the correct answer with out extra steps.

Any help to point me in the right direction would greatly be appreciated.

Thank You

Hi!

If I understand your task correctly, you are replacing the TODAY function in the formula with a reference to the cell where the desired date should be written. However, in order to get results on a specific date, your table must contain a column with dates. In this case, I assume there is no such column.

I am looking for a few formulas for the following.

- I have two start dates (Original and the a start date for the fiscal year), i have a formula that will calculate the current time in program in years/months based on the original start date.

-I have another formula that will calculate the total amount of time in program based on original start date and end date.

I would like to know if there is a formula available to ensure that the current amount of time in program will stop calculating to the current date if the 'end date' is entered.

I would also like to know how to get an average formula in months based on my column for current time in program for the table.

Please help.

Thank you

Hi!

Here is the article that may be helpful to you: How to insert today date & current time as unchangeable time stamp. I hope it’ll be helpful.

Hi there I"d love some help with either excel or google spreadsheets for enrolments for school.

I'd like to start with DOB (date of birth DD/MM/YYYY) + 5 years and then If the month is jan/feb/march/april then the result is 1 and if the month is may- dec then the result is 0

Example: 21/09/1966 + 5 years 21/09/1971 if the month is sept then the result is year 0

Hoping you can help

Hi!

You can find useful information in this article: How to add and subtract dates in Excel.

Hi I am trying to work on a budget and If I have a yearly budget of £100 000 for example then I should spend £8333 or less each month.

What I want to do however is use a formula that will keep updating as I enter in a new date each month.

If I only spend £8000 in January then I want the leftover £333 to be split up by the months remaining (11) and a equal portion given to each month, does this make sense? So the monthly budget keeps updating in line with spending, same with if I spend over budget, I want the amount I have overspent to be divided by the months remaining and an equal portion taken off each month.

I have a whole load of departments so this would take ages if I did it manually, what I want to do is input my spend for the month in each department and enter in the date (31/01/2023) and all the budgets update, hope this makes sense, would appreciate the help

Hello!

Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. To calculate your monthly expenses, you can use this guide: Excel SUMIFS date range formula - sum if between two dates.

If you have a specific question about the operation of a function or formula, I will try to answer it.

Hi Sir,

I am trying to pickup date from below data. Can you please help me . I dont know which formula i have to use with. i have 18 thousand rows to pickup the dates. Thank you .

Security: Test ObjectIAH10141/19/2023Tier 2 - Catering Cartfront

Security: Test ObjectIAH10241/19/2023Tier 2 - Catering Cartfront

Security: Test ObjectIAH10671/18/2023Tier 2 - Catering Cartfront

Security: Test ObjectIAH4451/5/2023Tier 2 - Cabin CompartmentN/A

Security: Test ObjectIAH11841/17/2023Tier 1 - Seatback Pocket3e

Security: Test ObjectIAH14641/29/2023Tier 1 - Seatback Pocket34C

Security: Test ObjectIAH13691/21/2023Tier 1 - Seatback Pocket3B

Security: Test ObjectIAH10841/28/2023Tier 1 - Seatback Pocket39c

Security: Test ObjectIAH13811/23/2023Tier 1 - Seatback Pocket5B

Security: Test ObjectIAH20931/25/2023Tier 1 - Seatback Pocket39c

Security: Test ObjectIAH10161/25/2023Tier 1 - Seatback Pocket37c

Security: Test ObjectIAH20671/30/2023Tier 1 - Seatback Pocket34C

Security: Test ObjectIAH62261/28/2023Tier 1 - Seatback Pocket7C

Security: Test ObjectIAH10341/25/2023Tier 1 - Seatback Pocket1e

Security: Test ObjectIAH13811/29/2023Tier 1 - Seatback Pocket20E

Security: Test ObjectIAH10141/15/2023Tier 3 - Water Heaterfront

Security: Test ObjectIAH16751/11/2023Tier 1 - Seatback Pocket3B

Security: Test ObjectIAH20671/18/2023Tier 1 - Seatback Pocket3B

Security: Test ObjectIAH22141/12/2023Tier 1 - Seatback Pocket3B

Security: Test ObjectIAH14391/12/2023Tier 1 - Seatback Pocket3e

Security: Test ObjectIAH10241/4/2023Tier 2 - Catering Cartfront

Security: Test ObjectIAH10021/12/2023Tier 1 - Seatback Pocket4E

Security: Test ObjectIAH5181/2/2023Tier 2 - Cabin CompartmentN/A

Hello!

Your data has no general pattern and is 9 or 10 characters long. Also, the text before the date is of different lengths. Unfortunately, I cannot help you.

Can this formula be used to always return the following 31 March from another date eg if I start with 1 April 2017 or 1 January 2018 is there a formula to return 31 March 2018 regardless of what the start date is?

Hello!

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

=IF(A1>DATE(YEAR(A1),3,31),DATE(YEAR(A1)+1,3,31),DATE(YEAR(A1),3,31))

Hi i want to calculate below duplicate excel dates only one time. ans should be total 8 days please suggest is his possible.

01-Jan-22

02-Jan-22

03-Jan-22

04-Jan-22

05-Jan-22

06-Jan-22

07-Jan-22

08-Jan-22

04-Jan-22

05-Jan-22

06-Jan-22

07-Jan-22

08-Jan-22

Hello!

The following tutorial should help: Get a list of unique values in Excel & extract unique rows.

Hello - Using Excel 365 I am trying to create a yearly planner workbook with separate sheets for each week. I have a start date in cell J5 of my first week sheet [Week1], I have then created a simple formula [=J5+1] for the next day, which copies out nicely for the remaining days of the week. It works great for the one sheet, but the next sheets [Week2, Week3, Week4, etc.] would need to look to the last day cell from the previous sheet to continue the dates. Example, Sheet [Week2] cell J5 would use [Week1] J29+1. I can make this work manually, but I would like to be able to copy the remaining weekly sheets of the year and have the formula update dynamically. Is this possible?

Hi, I have problem substracting for remaining days for large dates. For example, I want to calculate remaining days from date today 5-Dec-2022 until 24-Jan-2030, I used the simple formula "=delivery date - today()" but it appear "0" which is not correct. What is the formula for this kind of case. Please help me! Thank you.

Hi!

I have not been able to reproduce your problem. You can find more information here: Excel DATEDIF function to get difference between two dates.

HOW CAN I USE A FORMULA PICK UP THE DATES FOR TUESDAYS AND THURSDAYS ONLY IN A MONTH

Hi!

A1- date Tuesday

A2 - =A1+2

A3 - =A1+7

Select A2 and A3 and drag the fill handle marker down the column.

For more information, please visit: How to use AutoFill in Excel - all fill handle options.

Excel formula If Date is between July 1 2019 - June 30 2020 enter Fy20 and If between July 1 2020 - June 30 2021 enter FY21 and If between July 1 2021 - June 30 2022 enter FY22

Hi!

Look for the example formulas here: Excel IF statement between two numbers or dates.

Hi Brains trust,

I am using "online google Sheets" version of excel

And have created check boxes to track production of components.

I have used this formula =if(H3,Today()) in a seperate cell to track the date the check box was done, however, when a new check box is chosen along the process it changes all the existing dates to todays date also. Does anyone know how I can adjust the formula to keep the date each check box was ticked?

Thanks for your help

Hi!

To insert a date as an unchangeable time stamp in Excel, use the recommendations from this article in our blog.

i have 1 year water collection data. every 15 minutes interval data is recorded. it means every day 96 cells data is recording into excel. some times it will be missing 1 or 2 days of data. How can i identify directly in excel (missing data in particular time or data)

Hi!

I don't quite understand what you want to calculate.

Hello, I have the following data: Cities, order creation dates and order confirmation dates. I need to count the number of orders in each city with the same order and confirmation date. I'm counting with the countifs function, but I can't figure out where the problem could be, maybe I'm putting the function together wrong?

Hello!

You correctly use the COUNTIFS function to count values by conditions. There may be a problem with the date in the condition. I recommend this guide: COUNTIF formulas for dates.

I have tried many ways but I am not getting any result. How should the function look like to calculate how many values there are when the creation date and the verified date is the same:

Column A - City name, Column B - Creation date, Column C - Verified date, my function look like this: =COUNTIFS(A:A,G16,B:B,"=C2")

G16 is the city name for which all values satisfying the condition must be counted. The date format is Short date.

Hi!

Look closely at the syntax of the COUNTIFS function. The = sign is not used in function arguments. Also take a look at the link I gave you earlier.

=COUNTIFS(A:A,G16,B:B,C2)

Hi,

I used the Excel DATE function to calculate dates for example (=DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)) so when I enter say 1/1/2022 the next cell will automatically say 1/1/2023

Can I auto fill the formula to a blank cell? When I auto fill it to a blank cell a date (1/1/1900) appears. I want the cell to remain a blank cell but still have the formula in it so when someone enters a date the next dates will automatically appear. So if I enter say 1/1/22 into one cell 1/1/23 will appear next to it and so on.

Is this possible or will I have to put up with 1/1/1900 until I enter a date into the cell?

Hello!

Use conditional formatting in a cell with a condition formula

=D4=""

Set a custom number format in conditional formatting to not show zero: #,##0;-#,##0;

Add a condition to your formula:

=IF(D4="","",DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)))

I hope I answered your question. If something is still unclear, please feel free to ask.

Sir I have little complex requirement

In my below statement of account I need to pull the closing balance of any particular date how it will be possible, please help

below is my example

Date Description Debit Credit Balance

05/11/2022 Opening balance 6,200.00 6,200.00

05/11/2022 Paid to Raju 230.00 5,970.00

05/11/2022 Received from Vijay 550.00 6,520.00

06/11/2022 Cash Collection 8,345.00 14,865.00

06/11/2022 Food Expenses 258.00 14,607.00

06/11/2022 Travelling Expenses 879.00 13,728.00

06/11/2022 Paid to Stanly 980.00 12,748.00

07/11/2022 Trafic Fine 85.00 12,663.00

07/11/2022 Cash Collection 3,330.00 15,993.00

In cell my date is 6/11/2022 and in cell i need the result of closing balance of 6/11/2022, that is 12,748.00

How I can pull the closing balance from statement of account of any particular date

Please help me.

With regards,

Sunil Pinto.

Hello!

You can find the answer to your question in this article: XLOOKUP in reverse order to get last occurrence. This should solve your task.

Hello, I am trying to extract just the date from this format: 2022-01-01 13:23:59 +0000

I have tried to classic formulas but they don't seem to be recognising it. I have over 2,000 rows of data and so I need a quick fix to extract date only please?

Thank you!

Hello!

The following tutorial should help: Convert text to date and number to date.

Please try the following formula:

=DATEVALUE(LEFT(A1,SEARCH(" ",A1)))

We have a tool that can solve your task in a couple of clicks - Text to Date tool.

It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

.. lll

How I can get a different information of

different employees in different date in one

cell?

I need a condition like previous month of 9th to current date is this possible in excel and every time previous month will be changed like this is october then the previous moth is september if the month is december then need to extract data from november 9th to current date

When using the formula =datedif(A4,B4"m") the formula shorts me 1 month, example if I entered 01/01/2023-12/31/2023 the formula says that is 11 months not 12, how do I correct this?

Hi!

It has been said many times here that 12/31/2023 means 12/31/2023 00:00:00. Therefore, the date 12/31/2023 is not included in your time slot.

Hi,

I need to set up formula, that shows if "start date" less than 3 days from TODAY and show me urgent alert.

thank you,

S

Hi!

You can find the answer to your question in this article: If date is within last N days. I hope this will help.

how can i sum a column with a date range like for month of September and for one particular name(like how much bob spent in month of September 1 to 30th)

Hello!

We have a special tutorial on this. Please see: Sum if between two dates in Excel.

I hope my advice will help you solve your task.

Sir i need one help, exm: I have one date (20-Sep-2022) in A2 column, in A3 column i write down number 66 (days), i need date result after 66 days on A4 column. can you please help me this unction.

A2 A3 A4

20-Sep-2022 66 (show date after 66 days)

Thanks and regards

bdmauz

Hi!

We have a special tutorial on this. Please see: How to add and subtract dates, days, weeks, months and years in Excel.

Column J contains the date received. Column V (Review Date) has a drop down selection of "4 Months", "6 Months", "12 Months", "Indefinite". Basically I need a formula that will show the date to be reviewed in Column V depending on the selected drop down option. For "Indefinite" I want the cell to populate as 'Indefinte".

Thanks in advance.

Hi!

I don't quite understand your problem. The drop-down list places the value you select in the cell. For more information, please visit: Excel drop-down list – how to create, edit and remove.

Sorry, to be more clear. Say I received a notice on the 30/06/2022 and wished to review the notice in 4 months time. I would put 30/06/2022 in Column J (review date) and select "4 months" from the drop down in Column V (review date). My problem is that I need Column V to show the actual date to be reviewed after I select the option from the drop down, for example in this case it would be 30/10/2022 after selecting "4 Months". Is there a way to do this?

Hi!

As I said, the drop-down list will interfere with the cell in which it is located, the selected value. Based on this value in the neighboring cell, you can use the IF function with multiple conditions to make the necessary calculation.

For example,

=IF(V1="4 months",DATE(YEAR(J1), MONTH(J1) + 4, DAY(J1)), IF(V1="6 months",DATE(YEAR(J1), MONTH(J1) + 6, DAY(J1)),""))

For more information, please read: Add months to date in Excel.

Thanks for reviewing this one for me!

Hello!

I have a formula in column M (=$G3-35) to populate a due date. What formatting rule would I use to make the dates in column M turn red when the due date is at 35 days or less?

Thank you!

Hello!

We have a special tutorial on this. Please see Excel conditional formatting for dates & time.

(Start Date) 12-01-2022) (End Date 02-02-2022)=IF(AND(MONTH(G6)>=MONTH(A7),MONTH(G6)<=MONTH(B7)),

IF(MONTH(G6)=MONTH(A7),DAY(EOMONTH(A7,0))-DAY(A7)+1,

IF(MONTH(G6)=MONTH(B7),DAY(DAY(B7)),DAY(EOMONTH(G6,0)))),0)

Dear sir,.

like this i entered the formula in excel it works for calculating between two month day as per month but if the month of start and end date in the same month its not calculating kindly help please

It is for planing enginering to entered work down as per start and end date as per days of month

kindly request to help i am stuck in this please.........

Hi!

I can't test your IF with multiple conditions formula. You didn't explain what result you wanted to get. The formula refers to 3 cells and you have given 2 values. Explain in detail what you want to calculate.

Dear sir,

i Want to distrubute any quantity as per month days compare to start and end date if that start and end date is in between more than one month to be calculate how much days in each month

Hi!

What values are in G6, A7 and B7? What result do you want to get? Give some examples.

Hello. I am using Microsoft Office Pro Plus 2016. I am creating a worksheet that contains NAMES in Col. A and BIRTHDATES in Col. B. I have formatted the BIRTHDATE column as Custom m/d. Everything works well until I attempt to insert 2/29 in the cell. It quickly replaces the input with 2/1. I can insert 2/28 and it will accept this input. How can I make it accept the 2/29 date input?

Hello!

To insert the date February 29, you need to enter it in full - 2/29/24. February has 28 days by default. Please have a look at this article: How to insert dates in Excel.

I have a sheet (handicaps) that contains names, golf handicap (numerical). This sheet changes each week of our season. I have another sheet (competition) that when the name is placed in a cell it looks at (handicaps) for the correct number for that week. My problem is we have 20 weeks, each week we have a competitive match. Once the number is inserted each week I have to manually copy and paste the true value. Each week a persons handicap can change and if I do not put in the true value after my formula retrieves the number it is possible 2 or 3 weeks down the road the handicap in week one could change because the formula is still active if I do not copy and paste values. This would change results from past weeks.

So, I am looking at calculating my weekly columns by date, some how. Something like calculate formula only between May 4, 2022 to May 6, 2022 only. Then in the second week go from May11, 2022 to May 13, 2022 only. That way I could imbed all my formulas and only calculate in the time frame that I want.

This way I would not have to copy and past values each week and my handicaps can fluctuate with no concern that it will change any past or future calculations.

Thanks

Hello!

I have what seems like a simple google sheet/excel date function that I can't quite figure out.

I have a drop down menu with a "yes" option in column A. In column B, for each row, I want it to display todays date on the day the "yes" was selected in column A. is this possible?

Hello!

I recommend reading this guide: How to insert today date & current time as unchangeable time stamp.

I hope it’ll be helpful.

how to convert Aug / 19/2018 13: 39: 09

to 20/08/2018 10:14 format ? anyone can help me

Hi!

To display the date in another way, use a custom date and time format:

dd/mm/yyyy hh:mm

Hope this is what you need.

I need to highlight the date in 1 column that is 14 days greater than the date in another column. What is the best method for doing that?

Example: Col D date is 24-Mar-22; and Col E date is 17-Apr-22. I need to determine and highlight Col E if it is 14 days past the date in Col D.

Thanks!

Hello!

Use conditional formatting formula:

=E1-14>D1

Hope this is what you need.

Yes, that works great! Better than the =Days(E2,D2) and then looking to see which rows have 14 or more days between them

How do I count the number of years worked since a specific date while accounting for a start date after the specific date?

Kind of like "Years worked since "A Date" if start date is "B Date"

Hello!

I recommend reading this guide: Calculate age on a specific date.

I hope it’ll be helpful.

It didn't unfortunately. I've been searching for a formula for this for 2 days. I may just have to work it out myself.

hi I want to count how many cells are in column B past due

Hi!

To count the number of dates by condition, I recommend using the guide - Using Excel COUNTIF function with dates.

Please someone help with my problem. i want to design a column that if its paid, the whole row will be green. if partially paid, yellow. is it doable? thank you in advance

Hello!

I don't know how you define full or partial payment. Use these instructions: How to change the row color based on a cell's value.

This should solve your task.

Hi, can you help me? I want to design a column that if the date encoded is today, the color will change into green. If it's past today, change to red. If it's within this week, yellow. Something like that. I hope someone can help.

Hi!

Please check out the following article on our blog, it’ll be sure to help you with your task: How to conditionally format dates and time in Excel - built-in rules and formulas.

I hope it’ll be helpful.

If in excel sheet coloum a mentioned as a feb 22,2021 so how we get in cloum b as a day name.

Hi!

Check out this method on how to convert text to date.

Hello, hope you could help me, with this

Target Date Date Submitted %

January 25, 2022 February 2, 20221 ????

How can I get their rate of their submission?

Thanks

Hi!

I have never seen a percentage of two dates calculated. What do you want to count?

Thank you for your reply,

I am getting their performance in %

They have their target dates to submit their data.

Their Target is January 27, 2022 and If they submitted on February 17, how can i get their absolute performance. This is part of their performance evaluation review.

Thanks

Hi!

You did not answer my question. If you explain how you understand "absolute performance", then I will try to help.

Hi,

I am trying to find out how to use a formula to find how many products have been purchased between two dates and then spilt them within products purchased (3 columns of products but i have not figured this part of the formula out yet).

I've tried to use the following formula but it doesn't seem to work: -

=COUNTIFS(E2:E88,">="&I94,F2:F88,"<="&I95)

Please can you guide me.

Kind regards

Saiqa

Hi!

I’m sorry but your description doesn’t give me a complete understanding of your task. I can't guess what doesn't work in your formula. The formula is written correctly, but it is not known what data you have. I guess this article will help you: Count unique values with criteria

If this is not what you wanted, please describe the problem in more detail.

I am trying to create a formula that will allow me to determine when someone is expected to have something done by. Example. they start of Sept 10, 2021 and they must have a specific course completed within 60 days of the start date. Everyone one has a different state date and I must know how much time remains before the 60 days is up.

Hello!

Here is the article that may be helpful to you: How to add and subtract dates, days, weeks, months, and years in Excel.

Please try the following formula:

=A2+60-TODAY()

Can you help me to convert this format 3.2021 (Month.Year) into 1-Mar-2021 please

Hello!

You need to convert text to date. The formula below will do the trick for you:

=DATE(RIGHT(A1,4),LEFT(A1,SEARCH(".",A1)-1),1)

Set the date format you need as described in this guide.

Hi im trying the following, can you help.

e.g ill write it phonetically

if today = January the sum A1

If today = february then sum A1 +b1

its for adding up monthly targets

Hello!

Here is the article that may be helpful to you: Using IF function with dates. Also you can use MONTH function. For example -

=IF(MONTH(TODAY())=2,A1+A2,A1)

I have the formula set as =DATE(YEAR(H2)+M2,MONTH(H2),DAY(H2)) and it is working in most instances where the date is the number in M2 is the number of years to add (i.e. the same date in 6 years time) .

I need a way to modify the formula so that the date shows a different date instead.

Basically, if the date in H is 07/08/2021 and the number in M is 6 AND it is an audit-able entry then I need the date formula to calculate our audit date plus 6 years which is 31 March (i.e. 31/03/2028 instead of 07/08/2027.)

Hi I have a question and cross my fingers if you can provide any help.

The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).

The 2 spreadsheet has only years (2018..2021) (A column).

I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

Thank you in advance. If you have any advice i am grateful .

Hi I have a question and cross my fingers if you can provide any help.

I have two spreadsheets with dates and data

The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).

The 2 spreadsheet has only years (2018..2021) (A column).

I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

Thank you in advance. If you have any advice i am grateful .