WEEKDAY, DAY and other functions to calculate days in Excel

In this tutorial, you will find lots of formula examples demonstrating the use of WEEKDAY, DAY and DAYS functions in Excel. See how to return a day of week from date, get the number of days in the year, find how many days are between two dates and more.

There are a variety of functions to work with days of year and days of the week in Excel, including WEEKDAY, DAY, DAYS, DATE and more. These functions can be used to determine workdays and weekends, calculate the number of days between two dates, count the number of days remaining in the year, return day of the week from date, and much more.

Weekday Excel functions are particularly useful for planning and scheduling, for example to determine the timeframe of a project and automatically remove weekend days from the total. So, let's run through the functions one-at-a-time and see how they can help you cope with various tasks in Excel.

Working with days of week (Excel WEEKDAY function)

Microsoft Excel provides a special WEEKDAY function to return the day of the week corresponding to a given date.

The result returned by an Excel WEEKDAY formula is an integer, ranging from 1 (Sunday) to 7 (Saturday) by default. If your formula's logic requires a different enumeration, you can start counting with any day of week, as you will see in a moment.

The syntax of the Excel WEEKDAY function is as follows:

WEEKDAY(serial_number,[return_type])
  • Serial_number - a serial number that represents the date, or a reference to a cell with a date or serial number.

    If the term "serial number" does not make much sense to you, let me remind you that Excel stores all dates as serial numbers beginning with January 1, 1900, which is stored as number 1. And it is these numbers that the WEEKDAY function operates on. If you want to learn more about how Excel stores dates and times, you may find the following article useful: Excel date format.

  • Return_type (optional) - determines what day of the week to use as the first day in calculations.

In your Excel WEEKDAY formulas, you can specify any of the following values for the return_type argument:

Return_type Number returned
1 or omitted From 1 (Sunday) to 7 (Saturday)
2 From 1 (Monday) to 7 (Sunday)
3 From 0 (Monday) to 6 (Sunday)
11 From 1 (Monday) to 7 (Sunday)
12 From 1 (Tuesday) to 7 (Monday)
13 From 1 (Wednesday) to 7 (Tuesday)
14 From 1 (Thursday) to 7 (Wednesday)
15 From 1 (Friday) to 7 (Thursday)
16 From 1 (Saturday) to 7 (Friday)
17 From 1 (Sunday) to 7 (Saturday)
Note. Though the WEEKDAY function is available in all Excel versions, from Excel 2013 to 2000, the return_type values 11 through 17 were introduced in Excel 2010 only, therefore they cannot be used in earlier versions.

And now, let's look at some examples of using the WEEKDAY function in Excel. All of the below formulas return the day of the week corresponding to March 19, 2015. For the sake of clarity, this date is stored as number 42082 in Excel.
Examples of using the WEEKDAY function in Excel

At first sight, it may seems that the numbers returned by the Excel WEEKDAY function have very little practical sense. But let's look at it from a different angle and discuss some formulas that solve real-life tasks.

Example 1. Excel WEEKDAY formula to find workdays and weekends

If you have a long date column in your worksheet, you may want to know which dates are working days and which are weekends.

An easiest way is to have a day name displayed in a cell, for example as "Friday, March 20, 2015" or just "Friday". And you can do this in no time by simply changing the date format. However, this is not always an ideal approach, firstly, because a short date format is often required, and secondly, because you may need to filter only weekends or only workdays in your Excel list.

One of possible solutions is embedding the WEEKDAY function into the logical test of the IF function:

=IF(WEEKDAY(A2,2)<6, "Workday", "Weekend")

In this Weekday formula, we set the return_type argument to 2, which corresponds to the week beginning with Monday (day 1). So, if the day of the week in cell A2 is less than 6 (Monday through Friday), the formula returns "Workday", otherwise - "Weekend".
Excel WEEKDAY formula to distinguish workdays and weekends

To filter weekdays or weekend days, you apply Excel's filter to your table (Data tab > Filter) and select either "Workday" or "Weekend".
Filtering working days or weekends in Excel

If your organization, or maybe some regional office of your company, works on a different schedule where the days of rest are other than Saturday and Sunday, you can easily adjust this WEEKDAY formula to your needs by specifying a different return_type .

For example, to treat Saturday and Monday as weekends, you set return_type to 12, since you want the "Tuesday (1) to Monday (7)" week type:

=If(WEEKDAY(A2, 12)<6, "Weekday", "Weekend")

Example 2. How to highlight workdays and weekends in Excel

The Excel WEEKDAY formula discussed in the previous example has no problem with finding working days and weekends, no matter which days of the week are days off. However, you can improve the visual presentation of the results by shading workings days and weekend days in different colors.

For this, you can create Excel conditional formatting rules with the following WEEKDAY formulas to highlight weekends or workdays, or both:

Highlight weekends (Saturday and Sunday): =WEEKDAY($A2,2)<6

Highlight workdays (Monday - Friday): =WEEKDAY($A2,2)>5

The results look much better now, don't they?
Use Excel conditional formatting to highlight workdays and weekends

Example 3. How to get a day of the week from date in Excel

If you have a list of dates in Excel and you aim to find a day of week for each date, you do not actually need any special formulas :) The point is that Excel already knows what day of week a given date is and all you need to do is get it to display that information.

For example, if your dates are in column A, you can put a simple =A2 formula in cell B2 and then copy it down to other cells. After that, you select the entire column B and set the custom date format to it, such as:

  • ddd - to display an abbreviated day name, e.g. Sun.
  • dddd - to display full day names, e.g. Sunday.

Displaying a day of the week in Excel

Please note that the results, which may look like usual text entries to you, are in fact fully functional Excel dates that you can use in other calculations. You can tell these are dates by their right alignment in a cell as opposed to left-aligned text values.

Example 4. Return day of week as a text value

An alternative way to determine a day of the week by date is using the Excel TEXT function. In your TEXT formulas, you can specify the same formats as in the above example - "ddd" to return a short name and "dddd" to return full names of the days of the week:

=TEXT(A1, "ddd")

=TEXT(A1,"dddd")

Where A1 is a cell with the original date.
TEXT formula to return days of week as text values

When using this method, please remember that the TEXT function in Excel always returns text strings regardless of the source cell's format. So, the day of week names you see in the above screenshot are text entries, and not dates. And this is what makes them different from the results of the previous example, which are still fully functional Excel dates.

Example 5. How to return a custom day name in Excel

Yet another way to find the day of the week from date in Excel is using the WEEKDAY function in liaison with CHOOSE. An advantage of this formula is that it lets you return the day names in any format of your choosing.

For example, if you want to display days of week as the first 2 letters, enter them as value arguments in the CHOOSE function:

=CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa")

CHOOSE / WEEKDAY formula to return a custom name of the day of the week

In this formula, the Excel WEEKDAY function gets the day of week as a serial number. And the CHOOSE function uses that number as index_num (the 1st argument) that indicates which value from the list of value arguments to return.

How to get the day of month from date (DAY function)

Microsoft Excel provides a special DAY function to retrieve the day of the month from a date:

DAY(serial_number)

The day is returned as a serial number ranging from 1 to 31. For example, in you have a list of dates in column A and you want to extract the days into column B, you put the following formula in cell B2 and then copy it down to other cells:

=DAY(A2)

Note. The Excel DAY function correctly handles dates in different Date formats, though the problems may occur if dates are entered as text. Please see How to distinguish normal Excel dates from text dates for full details.

An alternative way to extract a day of the month from a date is using the TEXT function, for example:

=TEXT(A2, "d") - to display day numbers without leading zeros, or

=TEXT(A2, "dd") - to display day numbers with leading zeros.

Note. The Excel TEXT function always returns text strings, not numbers (please notice right alignment of numbers in column B and left alignment of text values in columns C and D in the screenshot below). So, if you plan to use the returned days in calculations or other formulas, use the DAY function rather than TEXT.
The DAY and TEXT formulas to get a day of the month from date

How to calculate the number of days between two dates (DAYS function)

The syntax of the Excel DAYS function is so obvious that you will hardly need any explanations :)

DAYS(end_date, start_date)

Supposing that the Start Date of your projects is in column A and the End Date in column B, you can calculate the projects' duration (i.e. the number of days between two dates) using the following formula:

=DAYS(B2,A2)
Excel DAYS formula to calculate the number of days between two dates

This formula will work with normal dates as well as dates formatted as text. If either the start date or end date is a text value, Excel will try to convert it to date (you would use the DATEVALUE function for this). If Excel is unable to parse your text sting as a valid date, the DAYS formula will return the #VALUE! error.

Working with days of the year in Excel

When working with days of year in Excel, you have a few functions at your disposal. Which one to choose depends on your data format and exactly what result you are after :)

Example 1. Get the day number of the year (1-365)

This example demonstrates how you can get the number of a certain day in a year, between 1 and 365 (1-366 in leap years) with January 1 considered day 1.

For this, you use a combination of the Excel DATE and YEAR functions:
=A2-DATE(YEAR(A2),1,0)

Where A2 is a cell containing the date.
The DATE / YEAR formula to get the day number in a year

And now, let's see what the formula actually does. The YEAR function retrieves the year of the date in cell A2, and passes it to the DATE(year, month, day) function, which returns the sequential number that represents a certain date.

So, in our formula, year is extracted from the original date (A2), month is 1 (January) and day is 0. In fact, a zero day forces Excel to return December 31 of the previous year, because we want January 1 to be treated as the 1st day. And then, you subtract the serial number returned by the DATE formula from the original date (which is also stored as a serial number in Excel) and the difference is the day of the year you are looking for. For example, January 5, 2015 is stored as 42009 and December 31, 2014 is 42004, so 42009 - 42004 = 5.

If the concept of day 0 does not seem right to you, you can use the following formula instead:

=A2-DATE(YEAR(A2),1,1)+1

Example 2. Calculate the number of days remaining in the year

To compute the number of days remaining in the year, we are going to use the DATE and YEAR functions again. The formula is based on the same approach as Example 3 above, so you are unlikely to have any difficulties with understanding its logic:

=DATE(YEAR(A2),12,31)-A2

Calculating the number of days remaining in the year

If you want to know how many days remain till the end of the year based on the current date, you use the Excel TODAY() function, as follows:

=DATE(2015,12,31)-TODAY()

Where 2015 is the current year.

This is how you work with days of week and days of year in Excel. In the next article, we will explore Excel functions to operate on bigger time units such as weeks, months and years. Please stay tuned and thank you for reading!

You may also be interested in:

123 Responses to "WEEKDAY, DAY and other functions to calculate days in Excel"

  1. saehu says:

    what if I want to calculate the number of days between two dates but only for weekdays (exclude weekend)? thanks.

  2. misulu says:

    I need to set up a chart with backdating from an event date. Some of those days have to be on a Tuesday. Is it possible to set a formula so that the dates returned are always on a Tuesday, regardless of the day of the week of the original event date? Or, do I have to set each event individually, rather than cut and paste?

    • Anonymous says:

      Dependent on how your data is.

      Conditional format; =weekday(date(year,month,day))=3

      Anything in *s mean link it to relevant cell. I have numbers 1-31 in row 1 for example and months (Jan-15 format) then from B2 till the end of my table I have the conditional format on. The calendar is colour co-ordinated. The date formula only likes numbers, if you are going to link to the "Jan-15" then that part of the formula has to be; date(year(*cell*),month(*cell*),*day*)

      Sunday is day 1, therefore Tuesday day 3

  3. Cstone says:

    =IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the ether days. Thank you.

  4. Luvly says:

    Is it possible to only do certain dates and days of the week, say every Monday, Wednesday, Friday for a certain time frame? Thank you.

  5. Svetlana says:

    Hi,
    Great article, thank you. Not sure if I missed it - I need to count working dates between 2 dates and then working dates relating to the previous month. I can do it long way of cause, but maybe there is an easier way?
    example:
    25/07/15-28/08/15 -25 work days - 4 relates to July

  6. CONFUSED says:

    WE NEED TO MAKE A SCHEDULE IN THAT 2 PEOPLE WILL WORK EACH WEEKEND (TWO PEOPLE ON SATURDAY AND ONE PERSON ON SUNDAY ) THERE ARE 8 PEOPLE TOTAL TO WORK - HOW WOULD WE GENERATE A RANDOM ROTATION FOR SOMETHING LIKE THIS. IF IT IS NOT RANDOM THE SAME PEOPLE WILL WORK ALL HOLIDAY WEEKENDS
    THANK YOU

  7. Paula says:

    Hi, I want the spreadsheet to calculate as of the start of the working week (Monday) each time I open it. I use the now function which gives an indication but my data would be more accurate if dated as of the start of week. Can you suggest formula for this please?

  8. Mark says:

    Hi
    I want to calculate moving weekly average from the data column. The start date should Sunday ending on Saturday. So the average # should be same for that week and the same for the next week and so on. This is monitor weekly permit limit for a process plant data. I don't need rolling weekly average but moving fixed week average. Thanks.

  9. Melany says:

    Hi!
    Thank you for the article! In a range of dates I need to know how many days there are in one month and how many days in the following month!
    Example:
    30/05/2015-13/06/2015 : 2 days in May and 12 days in June.
    Is it possible? Thank you ;)

  10. Ashish Chauhan says:

    Hi Svetlana

    It would be a great help if you can tell me the syntax to use a date both as holidays and weekdays (As per situations).

    For example: 1-April-2016 is a Holiday, but if task is done on 1-April-2016 (start date) then the date should be included else excluded.

    Waiting for positive reply :)

  11. Ann says:

    I want to give employee reviews on the 90th day. If the 90th day falls on a SAT or SUN I want to give the review on the previous FRI instead. How do I use =IF(WEEKDAY function to give the date and not just the text FRI?
    For instance, the review date falls on 11/21/2015 a SAT (in Cell B5) but want to give revised review date of 11/20/2015 a FRI (in Cell C5) instead. Or if review falls on 11/22/2015 a SUN (in Cell B5) but want to give revised review date of 11/20/2015 a FRI.

    I know I have to subtract 1 (SAT) or 2 (SUN) but when I put -1 or -2 it doesn't work. Please I have tried many different ways to no avail.
    Thank you

  12. Abie says:

    What if I would like to see the text WE + the month and date (in numeric) to my cell? Like WE 11/02.

    Truly appreciate your help.

  13. Surjeet says:

    Hi
    I want to convert weekday name Mon, Tue, Wed to be converted to no of weekdaylike 1 for sunday, 2 for monday. Can you help me out

    • Hi Surjeet,

      If you have a date displayed as the weekday name, you can use a usual WEEKDAY function like =WEEKDAY(A1)

      If those are the text values, you can use the following nested IF's:

      =IF(A1="Sun", 1, IF(A1="Mon", 2, IF(A1="Tue", 3, IF(A1="Wed", 4, IF(A1="Thu", 5, IF(A1="Fri", 6, IF(A1="Sat", 7, "")))))))

  14. shiv says:

    i want to insert starting day and end day and date in excel as follwos

    start date 15.10.2015, end date 16.10.2015

    ans. i want 16-16.10.2015

  15. Amusjh says:

    If I have a Saturday date (12/5/2015) how do I figure out how to get a the Saturday date for 2016?

  16. Aziruzam says:

    Svetlana - I need your expertise as I am truly struggling to find the formula for “next week 1st working day”. Currently I am using this formula =IF(A1="","",A1-WEEKDAY(A1)+2+IF(WEEKDAY(A1)>=2,7)) and it works great where every result returned on Monday.

    However, the return date or that Monday is our Public Holidays so it is not going to be our 1st working day of the week. Is there a formula to include Public Holiday? I already have Public Holidays table set up.

  17. subramanyam says:

    Very Very Thanks

  18. Jesse says:

    Svetlana,
    Thanks for your tutorial, I want to do finding next the four firday, but without public hoildaym, ie. tomorrow is chrismax hoilday and 7 days later is 1 Jan, so the next four firday would be 8/1, 15/1, 22/1 and 29/1.

    How can i do it in execl? thanks a lot

    • Hello, Jesse,

      Please enter the formulas below to 4 different cells:

      =WORKDAY.INTL(A1,1,"1111011",$B$1:$B$3)
      =WORKDAY.INTL(A1,2,"1111011",$B$1:$B$3)
      =WORKDAY.INTL(A1,3,"1111011",$B$1:$B$3)
      =WORKDAY.INTL(A1,4,"1111011",$B$1:$B$3)

      So you'll find the 1st, 2nd, 3rd and 4th Friday. Please note that range $B$1:$B$3 should contain Public holidays.

  19. Hoo Truely says:

    I have a timesheet for our employee, weekends are highlighted so are US holidays based on countif formula, my dilemma is if a holiday falls on a Saturday I'd like the conditional formatting to highlight a day before instead and if the holiday falls on a Sunday, I'd like cf to highlight it on the time-sheet a day after, Monday. Please help, thanks,

  20. Tommy1 says:

    Hi experts, I'm looking for divine intervention!

    I need to be able to enter a date that will count back 14 days and show the Monday ahead e.g. enter 18 February 2016 and get a return of 8 February 2016, however at the same time if I enter a date that is a Monday it will show the previous Monday only e.g. enter 15 February and get 8 February.

    I tried using this formula but anytime i enter a date that falls on a Sunday it skips forward to the upcoming Monday...!!??!
    =IF(WEEKDAY(D4)<2,D4+2-WEEKDAY(D4),D4+2-7-WEEKDAY(D4))

    Make sense? Thanks everyone

  21. Kandan says:

    I have scenario such as if in a column contain date weekend or holiday we should get the previous working day.for example Date:04/30/2016 is a Saturday,so I should get the Friday's date (04/29/2016)if this results is a holiday then we should get Thursday's date( 04/28/2016).

    Please help me on this!

  22. Dilip Badhe says:

    Hi, Can some one help me to separate time bands as per below through excel formula for each day and date

    Weekday 1800-0000 hrs = Prime time
    Weekend 0600-0000 hrs = Prime time
    Weekday 0000-1700 hrs = Non Prime time
    Weekend 0000-0600 hrs = Non Prime time

    Week Day Hour PT:NPT
    Wednesday 0 NPT
    Wednesday 1 NPT
    Thursday 2 NPT
    Friday 3 NPT
    Saturday 4 NPT
    Sunday 5 NPT
    Monday 6 NPT
    Tuesday 7 NPT
    Wednesday 8 NPT
    Thursday 9 NPT
    Friday 10 NPT
    Saturday 11 PT
    Sunday 12 PT
    Monday 13 NPT
    Tuesday 14 NPT
    Wednesday 15 NPT
    Thursday 16 NPT
    Friday 17 NPT
    Saturday 18 PT
    Sunday 19 PT
    Monday 20 PT
    Tuesday 21 PT
    Wednesday 22 PT
    Thursday 23 PT
    Friday 24 PT
    Saturday 25 PT

    Thanks in advance

  23. Dilip Badhe says:

    Hi, Can some one help me to separate time bands as per below through excel formula for each day and date

    Weekday 1800-0000 hrs = Prime time
    Weekend 0600-0000 hrs = Prime time
    Weekday 0000-1700 hrs = Non Prime time
    Weekend 0000-0600 hrs = Non Prime time

  24. Jean-Marc C says:

    I am trying to create a dynamically updated calendar based on a given date propagating to a 6 months individual calendar grids. I am trying to find out how to auto insert the correct first day of the month under the correct day. Example my project starts Feb 20 so the first calendar grid would show February 1st as the Monday so the second cell from the left of the calendar since my week starts on Sunday.

  25. GAURAV AGARWAL says:

    IF DUE DAY IS SUNDAY. HOW CAN I FIND -1 DAY SATURDAY.

  26. Michelle says:

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

  27. Dinesh says:

    Hi,
    I am a Marketing Executive. We have monthly tour plan. So I am trying to make up a formula to check for the current month and as per that the routes will be added to the corresponding dates and Sunday's has to be left blank. Format is
    Month Date Route
    Can you suggest how to use the formula for this

  28. Suneel says:

    I need to make 2 drop downs. One with selection of week 1 to week 8 and I want to limit the date in other drop down. Example if I select week 2 in drop down then other drop down should give me only option of dates starting 6th June to 10th June.
    Please tell me if that's possible.

  29. Sarah says:

    Great article, thank you! I'm working on a pretty simple spreadsheet, but I've run into an issue with returning the weekday in a column. The formula below works great if there is data in column A. Is there a way to leave the weekday column blank if no date is entered in column A?

    =TEXT(A2, "ddd")

  30. Harshal Sonawane says:

    =IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the Tue, Thur, Sat,Sund (1st and 3rd) and 2nd Sun And 4th Sun "Meat" Thank you.

  31. Harshal Sonawane says:

    =IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the Tue, Thur, Sat,(1st and 3rd "Sun") and 2nd Sun And 4th Sun "Meat" Thank you.

  32. edz says:

    need to calculate total income for the week.. need to get the total of mon to sat of the week

  33. ICE says:

    Hi,
    Good day.
    I want to know what formula I can use to count the number of a day in a month (ie. the number of Mondays in June2016 or the number of Tuesdays in July2016, etc.) or is there such a formula for this?
    Thanks! :)

  34. samuel says:

    Hi,

    Please i need you to help me use my spreadsheet to automatically do remaining number of days or countdown between two dates. e.g 02/feb/2016 to 20/June/2016 and for everyday it will automatically update the remaining days to get to 20/June/2016.

    Thanks

  35. Ranh says:

    Hi,
    I am working for a project and for that project we need to calculate number of months between two dates in excel. For instance, I need to calculate the month between: 00-Jan-1964 and 00-Mar-1966. As you can see we don't have exact dates in them. Most of the excel functions that I found online work when I put exact dates in it. But we don't have any exact dates at our disposal. Any help would be greatly appreciated!

  36. EDD says:

    Hi
    I am looking to add 39 calendar days from a date (i.e. incl. weekends).

    But if the date falls on a Friday/ Saturday/ Sunday, i am looking to show the Thursday before.

    Would anyone be able to help, please?

    Thanks

  37. Benjamininfo says:

    hi,
    can you please help me.
    how can i know which day it was 100 days ago.
    is it correct: =today-100 ?

  38. Kishore says:

    How can I get the 12 week prior Friday from the current date?

  39. Gundu H Danolli says:

    How to apply sum formula for week wise & week starts from monday & ends in sunday, is there any formula in excel which return the sum value week wise that to which automaticaly calculate the sum of Monday to Sunday in the perticular month.Please help in this.

    Regards,
    Gundu H Danolli

  40. Winnie says:

    I have a timesheet which weekends are highlighted,I'd like the conditional formatting to highlight a day after, Monday, if holiday falls on a Sunday
    Hope can get you help, thanks.

  41. Junaid says:

    How to list out every Thursday in between two dates? thank you in advance.

  42. yaash says:

    hi guys, I have a question. I have a particular start date and a list of other dates.
    I am required to extract 8 WEEKNUMS starting from my start date followed by the list of dates given. Again, i'm only required to extract 8 weeknum,
    how to i do it?
    help on this is much appreciated. Thanks all :)

  43. YBW says:

    If I have the number of days it takes for x amount of money to run out, how can I get the exact day to be calculated in a formula?

    Thank you in advance.

  44. sarah says:

    I need a formula, where I put one week in one cell, ie, January 2-6, 2017 and I have 5 other cells for Monday, Tuesday, Wednesday, Thursday, Friday, is there a forumla that I can input and it will automatically put 01/2/17, 01/03/17, etc.? Please help. Thanks.

  45. ANAND says:

    HI,
    I need a formula, where I put today's date in one cell, ie, 25/11/2016 and get exact date of after 15 months ie, 24/02.2018? Please help.
    Thanks.

  46. michael says:

    I need a formula to calculates the weekend dates only

  47. Vincent says:

    I'm trying to make a calendar to give my police officer son in law for Christmas. I've not given up but so far drawing blanks.

    He works 3 days on and 3 days off. I could just mark the days he works on a calendar. The problem is sometimes they make adjustments to the days he works, not often but it does happen.

    I need to be able to mark (for example) the last three days he worked. Then have it highlight his future work days based on that. Iow, high lite 3 days, skip 3 days but be able to modify the days and it auto adjust.

    My brain feels like scrambled eggs :-( any ideas?

  48. becha says:

    please help with formula. i want to count based on several criteria so i use countifs. the dates are entered as 1/2/17, 2/3/17, etc.

    what is the formula to count the date if it falls on a Monday, or a Tuesday? this is what i have tried, which does not work:

    if(weekday(A:A,1),2)

  49. Dee says:

    hi,
    I need to make a table for the office lottery which needs to be paid every Wednesday for the year. I need a table that has employee names in the first column and the Wednesday dates for each month across the first row. How do I do this?

  50. Aamir Mahmood says:

    I have this chart in my report, is it possible that if i put data daily in count daily colum and it automatically add in the the weekly and monthly colum and that weekly coloum become zero on every sunday.

    Looking forward for your kind response.

    Count-Daily Count-Weekly Count-Monthly
    TRA/JHA 10 10 95
    RADARS 26 26 384
    RADARS-Closed 26 26 384
    ICR Entries MGR 1 1 33
    SOP/JHA Reviewed 10 10 95
    BMS INSPECTION 4 4 47
    PRE-TASK(TBT) 30 30 421

  51. AK says:

    Is there a a formula that if i put a date it will give me what the date will be in 12 weeks time for example

  52. MH says:

    Hello, I would like to highlight a cell as a marker for today and put word "Today" as an indicator for today in highlighted cell A1. When today is over, I would like that the word "Today" that already was in cell A1 moves to cell A2 with the same highlight, and it does not show in cell A1 because it belonged to yesterday's indicator.
    What is the formula? Thanks

  53. jenniferT says:

    Hi
    Making a capacity grid based on two things weekdays and the day.
    For instance at the top I have Week 1 with a drop down going to Week 13 Right underneath i want to have the corresponding dates according to the week and month. Example. February week 2- 2/13-2/17. I want to be able to change the week and the dates change with it automatically. I didn't put the month in but I guess that's important huh???
    Any advice would be great

  54. Nina says:

    Hi,

    I would like to ask how can I calculate the number of days in this scenario

    Item Qty Date In (start) Date Out No. of Days No. of Stocks
    A 1 2-2-2017 1
    B 1 2-2-2017 2-14-2017 0

    How can i make a conditions that will show if there is no date out they will count the days base on the date today.

    Hope you can help me.

    Thank you.

    nina

  55. Shameen says:

    Hi,

    I stuck with a formula here. I have A2 (Work Start Date) B2 (Traget Completion Date) and B3 (Actual completion Date)and delays in B4. I want to retun the following logical formula. Can you please help.

    So,B4 = If B3 is blank return TODAY - B2.

    Thank you.
    Regards
    SPD

  56. Sead A says:

    Hello,

    Thanks for the informative article. I was intereted to know how to get the list of dates of a year in each month but only till the end of each month. Eg. 1,2,3...29,30,1,2,3,...27,28,1,2,3,...29,30,31,1,2,3...
    Thanks

  57. Sead A says:

    For my previous question, I got this syntax: =F2-DATE(YEAR(F2);1;0; however, it only extracts all the date numbers in a year. i.e. 1,2,3,...364,365,1,2,3,...365,366,1,2,3,.... what I wanted was for a sytax that would extract the list for each days of a year recurring every month.
    Thanks for your support

  58. zakiya says:

    Hi,
    If I have a number let's say 11, how can I get number of weeks like 2 week, 2 days
    instead of 2.2

    Please help

  59. Jerome says:

    Hi,

    I have something to ask about the dates, because i want to make an automatic Present and Holiday for the days of Monday to Thursdays = Present and for Friday is = Holiday please help me

  60. hasan says:

    Hi,

    j juts need automatic date count, example if i type 1 in other cell have to show 1 days, if i type 2 it has to count 2 days,

    can you help me out please

  61. Dave says:

    Earlier you acknowledged:
    "Note. Though the WEEKDAY function is available in all Excel versions, from Excel 2013 to 2000, the return_type values 11 through 17 were introduced in Excel 2010 only, therefore they cannot be used in earlier versions."

    So what are the differences between the single digit and two digit Return_types? For example, 2 appears to do the same thing as 11.

    • Hi Dave,

      Absolutely so, 2 does the same thing as 11. The difference is that 11 can be used only in Excel 2010 and higher versions, while 2 works in all versions of Excel 2000 to 2016.

  62. AJ says:

    We have set meeting dates throughout the year (weekdays only), notice must be given 10 days before and then 3 days before. The notice needs to be given on a weekday. I am very new to formulas and find this a little over my head. Are you able to give me a formula or a step by step?

  63. John Adams says:

    What formula can I use to pull next weekday to order from a list 246 (day of wk) for example sunday is 1 for us. So I want to get next order date from my list. Then I want to add a formula to get delivery date if it is 10 days add if holidays in between. Last there is a third factor some times we don't order cycle we order every 14,21 or 28 dat which we would add to delivery. Tired counting which day do we order next and adding delivery led time if we can accomplish in formulas.
    Thanks for the help, going in circles to figure it out.

  64. Bijeta says:

    Today i went for an interview.the interviewer asked to me suppose the employee joins today. after 3 month i want to find out the employee joining date.which method is used in excel.please tell me actually i didnot answer the question.

  65. Kalpith Navlakha says:

    I need to set up a table with backdating from an event date. And in that if any of those dates are Sat/Sunday, it should automatically change it to Friday. Is it possible to set a formula so that the dates returned are always on a Friday for Saturday and Sunday.

    For Eg: 20th May and 21st May is the weekend. So if i enter that day, it should automatically change to 19th May(Friday).

  66. Richard says:

    For many years I have recorded the date on which my roses bloom each year. The date is in the format dd/mm/year. I now want a formula that runs through these dates and identifies the earliest date and the latest date on which the roses flowered. My current formula always returns, say, 01/06/2012 as earlier than 01/05/2015. I want to be able to ignore the year and find a formula that in my example records 01/05 as earlier than 01/06. In other words, when searching for the earliest or latest I want to ignore the year and just focus on the days and months. Any ideas?

  67. Maria says:

    Can you please help me with my problem? I have a listing of 2016 (ie) daily LIBOR rates. I need to figure out the LIBOR rate 2 business days before the 15th (i.e) of each month. Is there a way to set up a conditional formatting or a formula for this? Appreciate any help. Thanks in advance.

  68. Arpan Datta says:

    THANK YOU, I NEEDED THIS.

  69. Abubakar says:

    Please can you help me with a formula in excel that will show me if the date I enter in a cell is weekend. Specifically, I need Date error message to be return if date is sat or Sunday. Thanks

  70. dany says:

    please help me.
    if i have only a one date and when the date is complete numbers is increase day by day when the date is increase and highlight the cell in excel.

  71. Rebeca says:

    Please advise, I need to calculate the payment day for a vendor that has terms of 45 days , excluding weekends but and this is the but, if payment is set a Monday move to Tuesday . Is just one vendor with this criteria.

    Thank you

  72. Kix says:

    please advice.
    I'm creating a column that the result of dates come up on Tuesdays (on or before only) from another cell.

    (cell A2)
    Monday, August 28, 2017

    (cell B2) my formula:
    =IF(A2="","",A2-WEEKDAY(A2)+3)

    (cell B2) result:
    Tuesday, August 29, 2017 (I want this to be-
    Tuesday, August 22, 2017)

    but on another date like-

    Thursday, September 21, 2017

    (using the same formula as above, the result is correct or the way I prefer it which is-

    Tuesday, September 19, 2017

    I'll greatly appreciate your help.

  73. Ramesh says:

    Hi,
    Please help me out to below question.

    If you have two id that condition is Eligible otherwise not eligible

    Voter ID Pan No. Aadhar No. Result
    45786 8022331 Eligible
    321321RD Not Eligible
    151165R 888211221 Eligible
    8825645 Not Eligible

  74. Arun says:

    if weekday of a date cell is falling on monday or Wednesday or Friday the cell should display a content or if the weekday of a date cell is falling on Tuesday Thursday Saturday I want to display another content.

    • Arun says:

      What can be the formula for this

    • Arun says:

      Hello,

      IN SHEET1 I have the falowing list.
      M W F T T S
      A1 = NAME 1 b2 = NAME 2
      A2 = TOPIC B3 = TOPIC 2
      A3 = 25-AUG-17 B4 = 13-SEP-17
      A4 = 20-DEC-17 B5 = 28-OCT-17
      A5 = NO.STU B6 = NO.STU

      In SHEET2 I have heading from 1-Jul-17 - 31-Dec-17 in each cell.

      as per date in a3 to a4 a i want to display the content available :
      C2 = SHEET1!A1 IF date in c1 falls in b/w 25-aug to 20 dec 17
      C3 = SHEET1!A2 IF date in c1 falls in b/w 25-aug to 20 dec 17
      C4 = SHEET1!A5 IF date in c1 falls in b/w 25-aug to 20 dec 17

      D2 = SHEET1!B1 IF date in c1 falls in b/w 25-aug to 20 dec 17
      D3 = SHEET1!B2 IF date in c1 falls in b/w 25-aug to 20 dec 17
      D4 = SHEET1!B5 IF date in c1 falls in b/w 25-aug to 20 dec 17

      I want a single formula to be applied for all cell in a row.

      PLEASE REPLAY

  75. vinitha says:

    how to calculate week day for example present=23,leave=8,night shift=2
    week of=4 then salary-19000 ,so how can calculate salary and how to reduce week day

    • Hello, Vinitha,
      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.

  76. Zahid says:

    Hi Dear,
    pls solve my below problem how i can calculate number of days without holidays. Please send me formula urgently.

    Wednesday,November, 01, 2017 to Saturday,November, 18, 2017

    • Hi Zahid,

      Please try to use one of the following formulas:

      1. =NETWORKDAYS(DATE(2017,11,1),DATE(2017,11,18),{"11/01/2017","11/02/2017"})
      This formula returns the number of working days between two dates, excluding weekends and any holidays specified in curly brackets.

      2. =NETWORKDAYS.INTL(DATE(2017,11,1),DATE(2017,11,18),1,{"11/01/2017","11/02/2017"})
      This formula does the same as the previous one. The difference is that you can adjust the calculation of weekends in the formula by changing the highlighted parameter if necessary.

      Hope this will work for you.

  77. Nadeem Art says:

    There are 3 shifts, Every shift needs change duty after a Week (Sunday) of the month through 24:00 hrs. as given below detail.

    Date: Friday,01/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Saturday,02/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Sunday,03/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Monday,04/12/2017 (Duty Shift would be Changed)
    Shift:A 16:00 TO 24:00 hrs (Evening-duty)
    Shift:B 00:00 TO 08:00 hrs (Night-duty)
    Shift:C 08:00 TO 16:00 hrs (Morning-duty)

    Please help me that how to set formula in excel that will show me Shifts: A, B or C will perform their duties on given date in 24:00 hrs.

    May kindly please be helped me in this case. in advance I shall be very thankful for him/her.

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

  78. Au Llemit says:

    Hi,

    Is it possible to compute a cell if date is weekday and not holiday or weekend? and compute a cell if date is weekend or holiday only?

  79. Alex D says:

    Hi,

    I need:
    to establish the average time taken between start and end dates/times across multiple rows.

    The start & end data provided is presented in two separate columns 'DD/MM/YYYY HH:MM' and I need the difference per row then an average of the total rows.

    I have tried:
    =INT(end-start)&" days "&TEXT(end-start,"h"" hrs ""m"" mins """) to extract the day/hour/minute difference between start and end per row but cannot find a formula that will allow me to work out the average based on these results.

    Result will need to be specific to the minute.

    Please can you suggest/advise.

    Many thanks
    Alex

  80. Laurie says:

    I need help I have a formula returning a date from another date but I only want it to return weekdays.
    Columns are
    Release date Training date
    3/15/2018 =DATE(YEAR(A2), MONTH(A2), DAY(A2)+2)

    It calculates 03/17/2018 in the field but that is a weekend.

  81. John Dickson says:

    I have a spread sheet where i have a cell for sum of time worked for Monday through to Thursday(N,18) with an adjacent cell(O,18) for Friday. I also have a date cell(I,1) using the =TODAY() with adjacent cell(O,1) showing the day (=TEXT(I1,"dddd"). I want to hide the value of N18 when (O,1) = Friday and hide the value of (O,18) when (O,1) when (O,1) = Monday-Thursday. I can't seem to get the right start on this.

  82. Ishant says:

    How to converte holiday in next working day

    9560429141

  83. Samad says:

    How to count days of a period without considering the days in thestarting date

    Ex: 01-feb-2017 to 7-dec-2017

    Counting to be started from 01-March-2017

  84. Samad says:

    How to count days of a period without considering the days in thestarting date

    Ex: 01-feb-2017 to 7-dec-2017

    Counting to be started from 01-March-2017

    Thank you

  85. AJ says:

    Hello
    I have a column with dates (only workdays M-F) starting from 1/2/2002 and goes all the way into 2009. I need to find out if there are any missing workdays in that column. Can you please help?
    Thank you

  86. vladimir cruz says:

    Hello, I am working with a report that is only returning M-F data, but I need it to also return Saturday and Sunday info.
    the formula i have is:
    =IF(AND(WEEKDAY(B3-1)1),B3-1,IF(AND(WEEKDAY(B3-2)1),B3-2,B3-3))
    how do i change it to give me the whole week? 7 dyas.
    thank you so much!

    vlad

  87. Rasha says:

    Hi,

    I'm working on students tuition, so if a student starts on a specific date then the tuition applied on that date for every month. I have a problem when a student, for example, has started on April 20, then May 20 is a weekend so the tuition is not applied how to fix that problem, please.

  88. Terrell says:

    =days() Sept 1, Sept 15th = 14 days in excel. It is actually 15 days. How do I count all days in excel?

    Thanks!

  89. Karen says:

    Hi

    I’m working on a table to calculate shift allowance. Every day,we have staffs working three shifts.

    In column A, it’s a weekday(Fri). So I need a result on Row 2, to display WD WA WN in their respective columns A1 A2 A3. Likewise for Public holiday (PH)

    Column A. Column B
    1 2 3 4 5 6
    Fri. PH
    Row 2. WD WA WN. SD SA SN

    WD = weekday day shift
    WA = weekday afternoon shift
    WN = weekday night shift
    SD = weekend or PH day
    And so on

    Thanks in advance

  90. Mahendra Singh Nayal says:

    If today is Friday so my value should be 30 otherwise value is 0. Date
    format is DD/MM/YYYY ( 01-May-1991).

    Example is below mention. I hope you give your response earliest.

    Date Results
    1-May-91 = if Friday = 30 other wise 0

    Note:- Friday is not mentioned in the data which i have require

    • Doug says:

      Mahendra:
      If you do not need to display the word "Friday" then this will work:
      =IF(WEEKDAY(A33)=6,30,0)
      Excel's normal setting is that Friday is 6.
      If you need to display the day's word it might be easiest to use a helper cell. In the helper cell you could enter:
      =CHOOSE(WEEKDAY(A33),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
      or a number of other variations on this technique all of which are explained here in AbleBits. Just search Weekday Function.

  91. Tammy says:

    I need a formula that will tell me if a certain date is the 1st, 2nd, 3rd, 4th day of the week.

  92. CJ Wrice says:

    I have an excel sheet Where I need one column to display a date and another collum to display what date is 4 days away but only count business days. For example, Monday to display that same Friday in the next column, Tuesday to display the following Monday's date, Wednesday the next Tuesday so on and so on.

    • Doug says:

      CJ:
      I think what you want to use is the WORKDAY function.
      Where the first date is in A18 and you want a workday 4 days from the date in A18 enter this in B18:
      =WORKDAY(A18,4)
      So, if the date in A18 is 6/7/18 four workdays forward is displayed in B18 as 6/13/18.
      If you need to know the day of the week 6/13/18 is then this will show it in C18:
      =CHOOSE(WEEKDAY(B4),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
      You can enter "Sunday" or "SU" or Sunday in another language.

  93. Aileen says:

    i need your help, our work days (Sunday to Thursday), once you input the date the following status should be done like these;

    1. if the closing date is today = status "Today"
    2. if the closing date before 2 days = status "Attention required!"
    3. if the closing date before 3 to 7 days= status "Still time"
    4. if the closing date past = status "Overdue"
    5. if the closing days are day off (Friday and Saturday) it should not be counted.

  94. Praveena says:

    I'm struggling with my Report, My requirement is based on Date Column in my Excel

    1. to fetch Today() and Today()+1 from weekdays starting from Monday to Thursday and

    2. For Friday, it has to fetch the row from friday,Saturday, Sunday and Monday.

    I have created a column index after date column and trying to apply the below formula, But it is not working. Could anyone please help?

    (A2 is my date column)

    =IF((AND(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=2,WEEKDAY(A2,1)=6,WEEKDAY(A2,1)=7)),"","Closed"),
    IF((AND(A2>=TODAY(), A2<=TODAY()+1)), "", "Closed")

  95. Cassandra says:

    I need a formula that will skip Sunday. I start with a date from another cell,"Friday, October 19, 2018". In the next cell I write =A3+1, but I must miss Sunday. How do I write a formula for this?

  96. Ramesh says:

    I need to convert week in to days. for example if I enter Week-23 in 52 weeks of a year automatically it will fill the dates 5 working days form Monday to Friday.
    Any one help please.

  97. Suki says:

    Hi,

    I need a formula which calculates if a person has worked on 2 consecutive Fridays in a month.

  98. Zohaib Ali says:

    i need the date range for example
    1-10-2018
    2-10-2018
    if 3 is Sunday then excluded the next day enter / delete the Sunday
    04-10-2018
    05-10-2018

  99. Faisal Nasir says:

    hi
    i want to calculate total working days in a whole month using conditions that where in the cell there is Saturday and sunday dont count it, skip it and count only mon, tue, wed, thu, fri as working day.
    how i can find working days using countif or any other fuction?
    please help

  100. Doug says:

    Faisal:
    You want to use an Excel function NETWORKDAYS or WORKDAYS. There is an excellent article here on AbleBits that explains these functions. The address is:
    https://www.ablebits.com/office-addins-blog/2015/05/20/excel-workday-networkdays-functions/
    or simply search the site for the functions.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28