1. D

    Calculate Weeks and Show Friday as Ending Date

    Hi, May I ask for help in writing a formula that will calculate and End Date that always lands on Friday based on a value entered into the Week Duration column. <tbody> A B C D 1 Task Week Duration Start Date End Date 2 x 1 Monday, Jan 7, 2019 Friday, Jan 11, 2019 3 y 3 Monday, Jan 14...
  2. S

    Rounding to the following Sunday

    Racking my brain to get the following to work: =IFERROR(IF(D5=0,"",D5+45),IF(D5="","",D5+7-MOD(D5-1,7))) D5 being the source date that I'm adding 45 days to and then rounding to the following Sunday. Honestly wouldn't mind figuring out the previous Sunday as well. We are trying to set...
  3. I

    Countif ignoring blanks weird table

    <tbody> A B C D E F G H I J K L M N F 1 Name Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday 2 John 8 AM 8 AM 8 AM 8 AM 8 AM OFF OFF OFF OFF 8 AM 8 AM 8 AM 8 AM 8 AM 3 David 8 AM 8 AM 8 AM OFF OFF 8 AM 8 AM 8 AM 8 AM 8 AM 8...
  4. U

    Is there a better way of solving this?

    I want to have the date give a Monday date. So 8/24/18 will be 8/20/18 but if 8/13/18, a Monday date, then it should show 8/13/18. I was going to create a cell for days of the week and write an if statement. this s y formula without the cell text <colgroup><col width="143" style="width...
  5. M

    Formula Help....Is this even possible?

    <tbody> BG BH BI BJ BK BL BM BN BO BP BQ BR Monday Monday Monday Monday Monday Monday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday Hrs Sub Rate Sub Total Sub Hrs Rev Rate Rev Total Rev Hrs Sub Rate Sub Total Sub Hrs Rev Rate Rev Total Rev ST 4 8 100 800 12 100 1200 8 100 800 10...
  6. B

    Getting a previous Monday (unless the date is a Monday)

    I saw a thread that asked for the prior Monday formula, but i used it and if the date is a Monday it goes to the previous Monday, How would i change the formula to be if Monday is today then leave it. I am new to using days of the week formula, so any explanation of how the formula breaks down...
  7. A

    Drag across to calculate first Monday of each month

    I need a formula I can drag across multiple columns, which will calculate the first Monday of the month increasing by one month each column. So far I've come up with the following, which seems to work but is over-engineered and I'm worried about it slowing down the rest of my sheet. Does...
  8. T

    [Formula Assistance] Date to weekday and sum

    Hi there, I've been searching the internet for a while and I find examples that I cannot use in my situation. Please let me explain what I am trying to achieve. Worksheet1 has thousands of dates in row A:A formatted as (date + time): 28-01-2018 18:55:20, and In worksheet2 I want to sum the...
  9. H

    Making presence list

    Hello, I am trying to make excel make me a list of my students based on the day. For example: I want excel to check who is here on Monday and show me the names in a list on "F"
  10. M

    Calendar Help

    I am trying to find out if it is possible to create an auto-populating calendar based on the first Monday of the month, or the third Tuesday of the month, etc. The calendar I need to create is for a vehicle inspection monthly calendar. I have 6 vehicles each day of the month that need to be...
  11. K

    Negate counts in COUNTIFS?

    I was wondering if you can negate counts in a COUNTIFS? Example: =COUNTIFS(A:A,A2,B:B,B2,C:C,C2) My formula is looking in all of column A (or Agent) with "John" as the Criteria. My formula is looking in all of column B (or Day) with "Monday" as the Criteria. My formula is looking in all of...
  12. D

    Help with Conditional Formatting Dates

    Hi All, Could someone help with with the condtional formatting of dates and differnet rules please. If A1 has a date of : Monday - Friday 07:00 - 21:00 and Saturday - Sunday 07:00 - 16:30 = B1 to be coloured AMBER Monday - Friday 21:01 - 06:59 and Saturday - Sunday 16:31 - 06:59 = B1 to be...
  13. T

    Where do I add the Return_type 11

    Hi I have this code =IFERROR(MATCH(INT(DATE(YEAR(year!$B$2);1;-2)/7)*7+2+AG5*7;year!$B$6:$B$40;0);"") at /7)*7 I can add a +2 for Monday and so on, but i'm getting some error with sunday because it's the first day in a week, i get wrong weeknumbers for that day/dates. So i need to add the...
  14. T

    Excel Weekday function, finding day in week

    Hi I'm a little lost here, so hope someone can guide/help me. I having a Sheet1 where I have 7 rows Monday to Sunday and week number 31 to 52. The week numbers i'm getting by the formula AG5 = Weeknumber(Year!B2,2) And then AH5 = AG5 + 1 and so on, up to week number 52. Then I have another...
  15. C

    Pushing a date to the next monday or tuesday

    If I calculate a Due date and it's a Sunday is there a formula to push it to the next Monday, if it's a holiday can I push it to the next Tuesday?
  16. U

    Hours/minutes between // networkdays.intl // different start/stop times in same week

    Hi there - here's the issue I'm having: I measure how long it takes our people to contact a lead that comes into our system. Currently, we track this Monday through Saturday, 9am to 9pm (Sundays are not counted). Here's the formula I'm working with...
  17. P

    Multiple Search and Find Problem

    Hi there, I'm struggling with a multiple search and find. I have 26 separate worksheets that are all in different languages. I am manually putting them into one worksheet (not sure if that's the fastest but I'm a newbie to all this). And then I need to get them all to be in English. So... for...
  18. C

    How do I find the last occurance of a number?

    The title really doesn't explain what is going on, I am writing a story and I use Excel for keeping track of the timeline What I am trying to do is show how much time has past between the dates in the same chapter, but I also want to see the dates in chronological order. Some dates get...
  19. M

    Lookup if two conditions are met!

    Hello, I'm trying to get this to work. I tried the vlookup, combination of Match & Index, but none helped me to achieve the desired output. I'm missing something. Here is my lookup table1 with "Holiday" details in the third column. Instead of "Holiday", the column might contains other...
  20. N

    Date from Week#, Year and Day Name

    Hi again, Is it possible to get the date from knowing the week number, day and year. I have a report that will give me data that shows me the following (except the last 2 columns that I am trying to work out): <tbody> 2017 2018 TY LY 18 Monday 3242 0 0 1 18 Tuesday 9873 2342 1 2 18...

Some videos you may like

This Week's Hot Topics