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.

Excel WEEKDAY function to work with days of week

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:

  • 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")


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 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:


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:


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:

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:

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:


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:


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:


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

222 comments to "WEEKDAY, DAY and other functions to calculate days in Excel"

  1. Gert Sluimer says:

    Be careful!
    =DAYS(01/07/2022,01/08/2022) gives 31, the number of days in July, so this is BETWEEN the dates: 01/08/2022 not being counted.

    =NETWORKDAYS(01/07/2022,01/08/2022) gives 22. that is the number of workdays in July 2022 plus the Monday 1 August 2022. So, in the NETWORKDAYS (and NETWORKDAYS.INTL) the end-date is included in the period.

    Be aware of this if you have to calculate the workdays in a period as a fraction of the number of days in that period, this can be dealt with by simply adding 1 to the DAYS function.

  2. Tim says:

    I have the below calendar formula and have been searching for days and days to insert a ceiling function to highlight every other Friday and have not been successful. "P9" is a cell that has "July 1, 2022" in it. Please help. Thanks.

    =TEXT(SEQUENCE(54,7,-MOD(WEEKDAY(P9,2),7)+1)," [>"&DAY(EOMONTH(P9,0))&"];;#")

  3. Muiz says:

    Hi, I would like to exclude weekend in my column, instead of i filter out weekend and weekday in new column, is there any ways to just filter in those column

  4. Bryan Franklin says:

    I have a spreadsheet with SUNDAY-SATURDAY tabs at the bottom for each day. I would like to be able to put the date in the SUNDAY tab and it auto-populate the date for the remaining 6 tabs (Mon-Sat). Is that possible? I've tried a few different formulas and can't figure it out.

    I am currently just going to each tab and changing the daily dates each week. It would be nice to just change Sunday and it populate the rest for me.


  5. Tom says:

    Is there say week 18 and Tuesday shown as 18.2 and plus 4 days show 18.6. After 18.7 next is week 19. Any formula to do this.

  6. Bill says:

    Please help. I track shipments per week of the year (week Num). The current capacity is shipping two orders per week. If there are more than two orders per week listed, what formula do I need so the orders greater than two are moved into the following week?
    Thanks for your help.

  7. Donald says:

    Can anyone help with what is wrong in this formula
    =COUNTIFS(B2:B5,">=EOMONTH(TODAY(),-2) +1",C2:C5,"N/A",D2:D5,"<=EOMONTH(TODAY(),-2) +28")
    it's return value is just zero. please help me.
    here is the data:
    B2:B5 =
    C2:C5 =
    D2:D5 =

  8. Susan Schroeder says:

    Hi! Given a column of dates in the form, month, followed by the year...such as...

    How can I separate the month from the year? I would like the months in one column, followed by its year in the next column. The goal is to create fields or "month" and "year" in a pivot table. Thank you.


  9. David says:

    Greetings from Ireland, is it possible to configure the WEEKNUM formula so that it yields a day of the Mon-Fri works well? For example, Monday would be "XX.1", Tuesday would be "XX.2", etc.?

    Thanks for your help. David

  10. Autumn-Mist says:

    Hey, I was wondering if you could possibly help me.

    I am trying to figure out the amount of days late a tenants rent is. I am using this formula but it is not working, am I doing it wrong?


    Basically, I just need to know if the tenant's rent is due on the 8th of any given month (for this we can say June 8 2021) and by today the rent still is not paid how many days late are they, including today. I mean I can do the math and I know it would be 178 days late, but is there a formula to put in the cell so it will calculate it automatically? Also, I think that if I just change the month in the following cells (ex July 8, 2021, Aug 8 2021) it would still work? Yes?

    Thank you in advance, any help you can offer would be super. I think if I were to use such a formula it would be super useful when I am sending the tenant reminder letters that their rent is late, so if they needed the file they could make a spreadsheet of their own if they wanted to.

    (Just because I feel it is important to add: No, I am not heartless, I am not trying to put tenants out on the streets, but looking to give them a breakdown of their rent so they can apply for help paying their back rent due to the current pandemic affecting them, if that is what they need.)

  11. Catherine Yap Shwu Wen says:

    I Has One Problem In Google Sheet.
    Why My Google Sheet Only Cell G29 & G30 Cannot Be Change To "ddd"?
    I'm Confuse.

  12. Neera says:

    I want to mentioned 0 on saturday or sunday of every month on rest date & weekdays reflects 1. how to do this in execl,

  13. Govind says:

    I have data in column A as below
    Tue .... and so on depending upon the working day of a person in shift.
    So practically speaking the working day of the next week is Tue and not Mon.
    How do i post in Column B that start of week day for that person.
    Thanks and regards

  14. Buddy says:

    Hi, I've spend many weeks on trying to find a solution to calculate number of days per week. My Sheet has twelve columns for each month of the year. Below each month I have 5 columns for W1, W2, W3, W4 and Week 5. I need to know number of days for week1, meaning from 1 Jan till first Friday. Then Week2 till second Friday (will always be 5 days) and so on for all weeks, except last week will be from last Monday of the month till the end of the month. Currently I do this manually for all 52 weeks of the year by looking at calendar.

  15. Gajendra says:

    Hi ther, plz help me on this,

    I have to do fortnightly payments (on Every alternate Friday) if i received a job card on monday or Thursday, that payment date( due date) should be reflect as alternative friday date on received date in excel payment sheet.

    Please help me on this.

  16. Supreetha says:

    Dear Sir,
    My doubt is quite a basic one. I have the Seven Days of the week. I want to now create a dummy for those days as

    Monday to Thursday = Weekday
    Friday to Sunday = Weekend

    How do I do that in Excel?

  17. GJ says:


    I want to identify last Thursday of each month from all Thursdays by a formula from given dates of style 04FEB2021,11FEB2021, and so on. Thanking you in advance.

  18. Amanda says:


    I have a very complex tracking sheet for my work.

    One of the functions counts the amounts of any specified day within a date range.

    I would like to add one more part to this formula and am not sure how.

    Current formula:


    Is there a way so that my formula will equal 9, (instead of 10) in cell AC when it is complete? The formula would need to use a list of populated holidays from another sheet titled “Lists”. That range of holidays is - F2:F5. When it calculates, it will determine that because the Day after thanksgiving is a Friday and falls within that range, it will automatically subtract an additional day.

    How can this be accomplished?

    Thank you in advance!

    • Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  19. Chet Greenway says:

    I have a production schedule on excel that the date column feeds off another cell. Basically todays date , plus the amount of time in the daily work schedule. Formula currently is = I3 +TODAY() So if today is 8/28 and I have 20 + jobs in lines I3,I4,........and so on. Each one adds up to .25 of a 8 hr shift. So my sheet shows 8/28 for the 1st 4 lines, on the 5th line it turns to 8/29 as it should as my 8 hr capacity is used up. My issue is I cant figure out how to take out Sat. and Sun and holidays from being populated as dates, therefore making it look like I can complete jobs sooner cause its using those 2 day we dont work on.

  20. John Campbell says:

    I need to generate a formula to where If Tuesday a cell value = 1 and if any other day of the week the cell value = 2.
    I have been trying to work with the =Weekday but so far, unable to get it to work.
    Thanks for any advice in working through this.


  21. raja gopal says:

    sir i need formula for create a list of working days dates ignore Saturday,Sunday dates require date list based on date in a cell reference (like 7/1/2020,7/2/2020,7/3/2020.7/6/2020,7/7/2020) thank you

  22. Cindy says:

    I have a cell with a date that is changed each week in A1 and formula to give me the day of week starting with Sunday ending Saturday and also give the date of each day based on the date in cell A1. The following formula is used in each cell C5:I5 =DATE(YEAR($A$1),MONTH($A$1),DAY($A$1)) C5 is Sunday Then D5:I5 adjust the Day($A$1+1) it's +1 for Monday,+2 for Tuesday,+3 for Wednesday,+4 for Thursday,+5 for Friday,+6 Saturday. My problem is since June only has 30 days it skewed the DAYS of the week but the DATE is correct, so it reads Sunday 28th Monday 29th Tuesday 30th Monday 1st Thursday 2nd Wednesday 3rd Thursday 4th. If I change the formula starting on the first thru 4th to be =DATE(YEAR($A$1),MONTH($A$1+6),DAY(I5+1)) then it works until there is a change from 30 to 31 day month.

    Is there a formula that will recognize and make the adjustment automatically?

  23. Michael says:

    Hi, please would you know the function or a way i can use to get the weekend (Saturday) date of a particular day in a week. Let's say i want to automatically get the weekend date of today (6/23/2020) in my cell. conditional formatting didn't work for me. Thanks in anticipation.

  24. Pau says:

    =OrText($B$3,"DDD") ="SAT",text($B$3,"DDD")="SUN" Hi.am trying to use this formula in conditional formating, to highlight Saturdays and sundays..can you please help me... there is something am missing... can you please help me

  25. Yuhi says:

    Can you help me?
    I need to add 2 days if the date falls on a Saturday and if it falls on sundays, we add 1 day

      • Pau says:

        =OrText($B$3,"DDD") ="SAT",text($B$3,"DDD")="SUN" Hi.am trying to use this formula in conditional formating, to highlight Saturdays and sundays..can you please help me... there is something am missing... can you please help me

  26. Claudine Gaudreau says:

    Hi, I am having an issue and I do not understand why this is happening.
    I am using Google Spreadsheet.
    My year begins on a Sunday, December 29th (cell B1) and ends on Saturday, January 4th (cell B4)
    Since the formula WEEKNUM refers to the date in cell B1, and is a Sunday, I omitted the type since the week begins on a Sunday, per Excel reference sheet: -> System 1: The week containing January 1 is the first week of the year, and is numbered week 1.

    But whatever I do, it always returns as week number 53 instead of 1, as it should be.

    The second issue is that the second week begins on Sunday, January 5th (cell J1) and ends on Saturday, January 11th (cell P1). Since the formula still refers to the Sunday of the first day of this week (January 5th), It returns as week number 2.
    That would be correct week number is the first week wasn't considered as week #1.
    Because of this issue, Week 1 does not exist and causes problems with futur weeks and years.

    I would be very grateful if you could help me find a solution to my problem.
    Thank you very much and have a good day :)

    • Claudine Gaudreau says:

      Sorry, I meant to say in my second sentence: "I am using Excel 2013, I got a bit distracted"

  27. noob123 says:

    I have a question and maybe someone could help me here.
    How can I make a formula to count for the whole year weeks and dates with more then 1 cell with the same date?
    Week1 01/01/2020
    Week1 01/01/2020
    week1 02/01/2020
    week1 02/01/2020

    Thank you in advance and have a nice day!

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. For example, Week1 01/01/2020 - is it one cell or several?
      It’ll help me understand it better and find a solution for you. Thank you.

  28. CC's Mama says:


    I am new to excel. Could you please help me? Is it possible to set a range by using "TODAY()" with a certain cell? For example: =SUMPRODUCT(--(WEEKDAY(TODAY():B34)={3,5}))

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      In the WEEKDAY function, you can record WEEKDAY(B33:B34). And in cell B33 write the formula =TODAY(). For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

      • CC's Mama says:

        I have a table of lunch expenses. I have dates of May on B4:B34, the expenses on E4:E34, and a fixed budget for the month. I need to sum all lunch expenses except Tuesdays and Thursdays and figure out the left budget for lunch expense to spend till the end of the month. So What I want is to sum all Tuesdays and Thursdays from today to the end of the month so every morning whenever I open the file I would know how much is left to spend for lunch. That formula is just a part portion. Sorry, if I didn't make it clear still.

          • CC's Mama says:

            Thanks for taking time to help me out. What I want to do though is to use "today()" within sumproduct fuction. I want the computer automatically calculate left days of the month from "today" excluding Tuesdays and Thursdays, not the amount of the expenses but the actually days.

            I need the mathematical number sentence like this:
            Lunch Budget ÷ (Days Left - (Tuesdays & Thursdays from the left days)*)
            * Since I have to check the left lunch budget each day I want to use "today()" function.

            I came up with this formula and it worked:

            P27 / ((NETWORKDAYS(TODAY(),B34)) - SUMPRODUCT(--(WEEKDAY(B31:B34)={3,5})))

            but, I want to make it like this:
            P27 / ((NETWORKDAYS(TODAY(),B34)) - SUMPRODUCT(--(WEEKDAY(today():B34)={3,5}))) or
            P27 / ((NETWORKDAYS(TODAY(),B34)) - SUMPRODUCT(--(NETWORKDAYS(today(),B34)={3,5})))
            but both won't work.

            I hope it help you to understand better as to what I want to figure out.
            Please help and thank you so much!

  29. cristian says:

    I try to get a result for the following example: if today is 04/14/2020, and last month 03/14/2020 fell on the weekend, what formula does it apply to bring me the next working day in March = 03/16/2020?

  30. Kiwi says:

    In weeknum suggestions why we have to use different numbers for monday like 2,3(0-6),11 and for sunday 1,17. For ex: for sunday we can use 1 right, why 17 also there for sunday, and where will have to use 17, is there any logic for using 1 or 17, like here we have to use only 17 for sunday but not 1, please let me know this, thankyou

    • Hello Kiwi!
      The list of possible values for the return_type argument in the WEEKDAY function is set by Microsoft. You simply choose a type from the list which suits better for your task. You can use any of the them as they just determine what day of the week to use as the first day.

  31. Rob Wickens says:

    Hi, I am trying something a bit different than what ever everyone else seems to be doing. I hope you can help me as I've been trying different ways for a very long time now and am getting nowhere fast.
    I am building a weekly schedule and can allocate weekdays easily. I am trying to enter holidays into this formula but it does not work. I don't care about how many days it takes. I am only concerned with what my end date will be (without weekends or holidays).
    This is what I've been entering so far..Example (=weekday,1). "weekday" being in the box next to it. then I simply follow through with that formula across the row and it self tabulates the weekdays only. When I try to enter a holiday with it...example (=weekday,1, B6:B12) "these would be dates in a different box", it does nothing. hope this makes sense. Please help if you can.

  32. Fred says:

    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?

  33. Tones says:

    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?

  34. Grace says:

    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?

  35. Bill says:

    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?

  36. Krupin says:

    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

  37. Arlene says:

    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.

  38. Raghul Kumar says:

    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.

  39. NikkiA says:

    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?

  40. Hai says:

    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?

  41. Gavin Chan says:

    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

  42. Pratik Patade says:

    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!!!!

  43. MEMEGAN says:

    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.

  44. yannick says:

    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.

  45. Nainan Varghese says:

    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

  46. James says:

    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 says:

      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) )

  47. nix says:

    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?

  48. Dildar Hussain says:

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

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

  49. Heidi says:

    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?

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 :)