1. G

    Index, Match when values the same

    I'm trying to setup a roster. In Cell K1 I have the date (which changes through drop down box). In column A I have a list of dates which some are the same date, Column C has Team 1 and Column E has Team 2 What I'm needing is when I change the date in Cell K1 it only shows Team A on that date in...
  2. L

    number based on the value of another column.

    Hi I have a column like the one below. I want to have another column beside it and each friday in column A will have number in column B as below. Is that possible to do it? Thank you very much. <colgroup><col></colgroup><tbody> Tue, 20/08/19 Wed, 21/08/19 Thu, 22/08/19 Fri, 23/08/19...
  3. M

    Contents of row deleted based on a zero value

    Please can someone help me with a script - I have a table 11 columns (D to N) and 7 Rows (Rows 7 to 13) - I would like the contents of each row removed if the value in G = 00:00 <tbody> Days Worked Start time Finish Time Time Worked Breaks Yes/No Breaks From Breaks To Total Breaks Total...
  4. D

    Getting a SUM with a cell when using the IF(OR Statement

    I'm using the following statement =IF(OR(C1="SAT",C1="Sun"),"Off","8") however I still want the ability to include that in a Sum. If the cell is a Sat or Sun the the cell =Off, if its not Sat or Sun then the value should = 8 This is a Calendar with required hours in each day, however on SAT...
  5. D

    Getting the SUM of a range of cells with formulas

    I need a way to have a cell be NULL if it is Sat or Sun I did find a formula to do this, but then it won't SUM the row and/or column <tbody> Site US1787377 Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Monthly...
  6. N

    Returnign largst or smallest value from a subset of values

    Hi there Am hoping someone could point me in the right direction with a quick one. I have a list as below, and am looking to return the smallest or largest value by day: A B Mon 154 Tue 192 Wed 216 Thu 147 Fri 126 Sat 98 Sun 48 Mon 52 Tue 69 Wed 210 Thu 99 Fri 137 Sat 104 Sun...
  7. Y

    EXCEL- Lookup Function to create a new Table from another table.

    Hello, In EXCEL using formulae in the example below, how may I populate Table1 with the data from Table 2. I tried Index Match but was unsuccesful. <colgroup><col><col><col><col span="6"><col><col span="2"><col><col><col><col><col span="2"></colgroup><tbody> TABLE 1 TABLE 2...
  8. E

    On-Call Schedule: Table Needs to Show Dates

    First of all I want to thank for everything you guys do, I have been able to find plenty of answers to many questions I’ve had in the past. Well let’s get into this conundrum, in short this is an On-Call Schedule, what I’m trying to accomplish is to for the techs to select their...
  9. K

    Yearly attendance

    <tbody> 2018 CFL's 2018 balance 2019 balance <tbody> 8-Feb-2019 2018 CFL's 2018 balance 2019 balance Jan-19 Feb-19 1-Jan-19 2-Jan-19 3-Jan-19 4-Jan-19 5-Jan-19 6-Jan-19 7-Jan-19 8-Jan-19 9-Jan-19 ID Name P L A C T CL P L A C T CL Tue Wed Thu Fri Sat...
  10. J


    I am trying to insert an and/or argument into countifs but can't get it to work. I tried SUMPRODUCT(ISNUMBER(MATCH,RANGE,CRITERIA,0))*ISNUMBER(ETC)) Can't figure out how to do it. For example <tbody> Student A GPA SAT ACT Student B 3.0 900 21 Student C 3.4 1200 Student D 3.0 1080 25...
  11. C

    Auto comments if dates match

    Hi everyone, is there a way to add comments based on dates? I have a file with three sheets. The first is called "2018" and is used as a calendar for year 2018 and other data. The second is called "2019" and is used as a calendar for year 2019 and other data. The last one is called "'Events" and...
  12. A

    Create Calendar automatically

    Hello, I'm hoping that someone can help if possible please. I have created a tracker for tasks performed across our department. Each month has 5 rows (but this can increase depending on how many tasks come in) and the days are listed across in the columns. Each year, we have to re-set the...
  13. M

    Find Date

    Hi All, Please find the attached file, where I am finding date in column 'C' and here condition, if sat and sun comes so ignore the date and jump on Next working day but for Monday it will jump on wed because 09Jul and 10 Jul task already been assigned. Regards Mohit <tbody> Day Date1...
  14. S

    Vlookup, when found, lookup value in diff column, based on value return X or blank

    As I was typing the title it seemed very complicated, hopefully below info will clarify it :) Each record has unique Reg# Given the data below, my formula in Destination sheet in cell B1 would return "X" if Packing Rating="Sat." otherwise return blank. Same type of Formula in Destination...
  15. D

    End time calculation with working hours

    Hi Experts, Please help this senerio Received time : 12/may/2017 8:40:00 AM I want to calculate 8 hours Tat with working hours ( 8:00:00 Am to 5:00:00 PM). sat and sun week off..please help
  16. K

    Calculating Time

    Hi, i am having trouble to calculate time between two dates. I managed to get my formulas right to calculate time when date range is same day or two days, but i cannot find robust formula to skip "OFF" days or if date range is more than two days. Please see below data.I would like to calculate...
  17. C

    TimeSheet-Calculator overtime Excel

    I have a question i get pays weekdays 8 hrs then overtime all day Sat Multiple Times @ 1.5 all day Sun Multiple Times @ 2 so weekday Multiple Time 1 then overtime @ 1.5 is there a way to have a TimeSheet-Calculator for a Month Calendar that will recognise Sat on its own and sun on its own, so...
  18. D

    How to extract hour and minte from cell

    Hello, I have a spreadsheet that gets sent to me every day. The time values are displayed as follows: "Sat 12:00 AM", "Sat 12:01 AM", etc. I would like to figure out a way to extract just the time (get rid of the "Sat"). I've tried using the conventional time extraction formulas, however it...
  19. F

    hope someone could help -Macro to assist averaging data set's over multiple worksheets

    I am hoping that someone can help me with a macro to average data from multiple time points Simply i have a 4 columns Day, Date, Time, Data The same format appears on each worksheet with in the workbook which i need sort and Average over the data range The data sort should be by date...
  20. W

    Date in one cell results value in another

    Hello. I am seeking a formula that will recognize a date in one cell and show the week of the month it is in another cell. Please see the following examples- thank you, Michael. Date range 1-7= Week 1 Date range 8-14= Week 2 Date range 15-21= Week 3 Date range 22-31= Week 4 <style...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back