Comments on: Excel WEEKDAY function: get day of week, weekends and workdays

There are a variety of functions to work with days in Excel. The day of week function (WEEKDAY) is particularly useful for planning and scheduling, for example to determine the timeframe of a project and automatically remove weekends from the total. Continue reading →

Comments page 4. Total comments: 284

  1. Day 1 in Excel is January 1, 1900.
    Weekday of this date gives a Sunday.
    However, when you Google for a calendar image of January 1900 you get a Monday.
    Can anyone explain this anomaly?
    Thanks,
    Fred

  2. I have a range of random dates in column b3:b30 and wish to find the oldest date and convert to the corresponding Monday

    How do I nest and use the MIN and Weekday function in excel?

  3. I have problem when creating =WEEKDAY($A2,2)>5 for year 2020 Feb 28 cross over to Mar 01. My "Sat" did not appear. It turns out to be a "Sun". I've checked the cell, the date is 01/03/2020 but ddd turn out to be a "Sun". How can I solve this?

  4. I am using conditional formatting to highlight weekends. If the first falls on a weekend it does not highlight. I am using =OR(WEEKDAY($A1)=7,WEEKDAY($A1)=1). What is wrong?

    1. Hello Bill,
      Since 30 and 31 of December is the 53rd week of 2019 and 1st to 5th January is the first week of 2020, the formula for this particular week needs some modification. Please try the one below:

      =IF(OR(WEEKNUM($A1, 1) = 53,WEEKNUM($A1, 1) = 1), TRUE)

      Hope it'll work for you.

      1. Thanks for the tip, but it didn't quite work. I am using a new spreadsheet for the year. However, I did get it to work by starting it in the correct cell (not $A1 but $A2). I like your site, I have learned a lot from it.

  5. Sir..i need to formula.one cell i enter month and i get result in column sequences ..wed..thu..sun..wed..thu..sun..wed..thu with date for this month

  6. Can I actually do this? :)
    I have this format in the cells for date and time:
    04:35 PM EDT Oct 29 2019
    How can I create a formula for finding how many minutes elapsed between cells in column A (arrival time) and column B (departure time)?
    I want to calculate how long my visitors spent in my office.

  7. Hi Team,
    I am working in a BPO industry. I need to track the production as well as the other details in day wise. For example The date is 1st November, in need to track the Target, Achieved, Production. So I have merged the 1st November header has been merged into three above cells. I do not have any issues on this. Please help me out to convert into weekly basis with the same header.

  8. I'm looking to calculate the date of the year based on a day.
    I'm doing some analysis using 31 years of day and have a mean value, 45 (i.e. day 45 of 365). I want to convert 45 to represent a date, so I don't have a generic year to plug in.
    What would be the excel formula to do this?

  9. How can i calculate the first (1st) day of each calendar month or the immediately preceding Business Day if the first (1st) day of such calendar month is not a Business Day?

  10. Is there a way to calculate the day of the week with the numbering 1-14 instead of 1-7?

  11. hi,
    i need to calculate age between 2 dates excluding weekends?

  12. Hi, I need to return a custom day (i.e S for Sunday and so forth).. I used the CHOOSE and WEEKDAy function but then the formula doesn't drag across and then follow the next day sequence. It's just copying the S from the cell before. How do I get this formula to work across the schedule I am creating? =CHOOSE(WEEKDAY(cell reference), "S", "M", "T", etc etc. Can you please help as I will using this to setup monthly work schedules

  13. Thanks, very helpfull

  14. Hello . I'm following all the functions from last week. You are Awesome because all your examples are very useful..

    I have once quick question for you , Please help me out because I'm stuck here.

    Question : For ex : I have a one date as a reference and from this reference date i need to get 3 different dates i.e ( 7 days before the ref date, 2 days before the ref date & 1 day before the ref date) but the twist is that the new date should fall on Weekday and not on Weekend( i.e Sat & sun).
    Please help me out , i'm trying to figure out this from many days.

    Thank you in Advance!!!!

  15. I am working on something and I need to enter a formula that counts a large number of days of the week that automatically adds up how many for example Fridays are in that list.

  16. may i get the answer in Microsoft Excel to these questions and reassure me if I can be selected. 1.sunday - Saturday + 4 Monday =? , Thursday - 3 Saturday + monthly total = 2. Summation = Sunday of the month divided by summation Monday of the month. 3. 40% of the Wednesday of the month multiplying by 20% of Friday. I'm counting on you really.

  17. Condition 1 should meet any text
    Condition 2 if the date exceeds or equal the current date (eg: 16th of any month), it should return a value 2
    Condition 3 if the date is between 1st and 15th of the same month, it should return a value of 1
    Condition 4 if the date is of the preceding month, it should return a value of 0

    My formula
    =if(and(a1="A", or(today()>=date(2019,3,16), 2, if(a1="A", or(today()=date(2019,3,1),1, if(a1="A", or(today()<date(2019,3,1),0)))

    This formula is not working . could you please let me know what is the syntax error

  18. Hello, I've got a problem with a specific formula.

    I have 2 cells generating dates:
    Todays date (using =now () ) - In the format of dd-mm-yy hh:mm:ss
    Working date (using =workday(todays date,5)+time(15,0,0) so it adds +5 to working date + declares time of 15:00:00. For my purpose, I need the format to remain dd-mm-yyyy 15:00:00.

    The issue that I'm having is that once the time passes 15:30:00 on today's date (=now() function) - I need the working date to increase from 5 to 6, so it becomes tomorrow's work.

    Can you please advise on this?

    Kind Regards,
    James.

    1. I forgot to add, this will probably need to be converted to an IF function, my cell references are:

      Todays Date = I2 ( =NOW() )
      Working Date = I3 ( =WORKDAY(I2,5)+TIME(15,0,0) )

  19. Say I want an "IF" formula that checks a cell (a1) with a date indicated then on a different cell (b1) it validates the indicated date on cell A1 that its already pass 3 business days once confirmed cell B1 would indicate as a "Yes" if not then a "No", is this possible?

  20. Dear sir i want to account the month. from start date to end date
    Example

    01-12-2018 - 01-06-2018 total 06 month

  21. If the date field is blank how do I get the return cell day of the week to stay blank? The =WEEKDAY(A2) formula is working fine and reporting as needed into the day of the week when the date is filled in, but if the date cell is blank yet (haven't added that data line yet) how do I get the day of the week cell to stay blank also?

    1. Try:

      =iferror(weekday(a2), " ")

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

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

  25. Hi,

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

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

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

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

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

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

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

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

    1. Tammy:
      There is a complete explanation of this topic here:
      https://support.office.com/en-us/article/WEEKDAY-function-60E44483-2ED1-439F-8BD0-E404C190949A
      Essentially you enter the date you're interested in and either accept Excel's default return type using Sunday(1) through Saturday(7) or enter the optional return type. It looks like this with the dat in A1:
      WEEKDAY(A1) with the default return type or WEEKDAY(A1,2)
      with return type 2. Return type 2 is Monday(1) through Sunday (7).

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

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

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

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

    Thanks!

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

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

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

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

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

  40. How to converte holiday in next working day

    9560429141

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

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

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

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

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

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

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

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

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

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

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

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

    2. What can be the formula for this

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

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)