1. M

    Trouble with IF Formula & Comparing Dates

    Hi everyone, I am working on a case management tool and am trying to identify 6 different types of renewals by comparing submission month, day, and year between two different cells. D2 is the certification period end date (renewal date), and D4 is the date we received the renewal form. I have...
  2. R

    Macro to add month sheets containing days

    Hi, I have a VBA code (see below) for a macro button to add month sheets in a workbook. Now the month sheets are sorted from January to December, but I would like them to be sorted from December to January; while still keeping differently named sheets at the end of the worksheet tabs. After...
  3. Z

    Find the Distinct Count of most common day for products used by day of the week (1-7) distinct count

    I have bike share data that indicates the day of the week a bike was used in number format 1-7 (indicates days of the week). I want to figure out the most common days of the week a specific route was most popular i.e. for each specific route which (number or) day was most common. I believe what...
  4. P

    Calculating days of supply using DAX...2

    Hi all Regard to Calculating days of supply using DAX... thread I would like to ask you for help. I need to calculate Days of Cover. Data structure is following: WeekDate, Product, ClosingStock, Demand. I tried to use the code from previous thread but I hit on problems. First one is on the line...
  5. F

    DATEDIF to show both positive and negative years, months and days

    Hi, I am trying to use a formula to show both positive and negative years, months and days between two date columns. I can get where I want showing a positive or negative number of months using =IF(L2>M2,-DATEDIF(M2,L2,"M"),(DATEDIF(L2,M2,"M"))) Where column L is "Retail Sale Date" and column...
  6. S

    How to Stop Excel from Calculating the Days Between Two Dates if Cell is Blank?

    We often calculate the amount of days between two dates for calculating interest (=DAYS(A22,A21) OR =A22-A21), but if there's no date in the cell below (i.e. the end date) it will show as a negative number (see image). I guess we would need the formula to assume if the cell is blank that it's...
  7. A

    Need To Get Elapsed Time Between 2 Dates During Working Hours ( All Days Included )

    Hi I need to get the elapsed hours between 2 dates considering that working hours is from 8 AM To 4 PM all days included ( no week ends or holidays ) example : A1 : 11/11/2019 09:31 AM B1 : current time { now() } will be used
  8. S

    Calculate no of days to go back to reach a specific day name

    I know this should be easy, but I keep tying myself in knots over this one. I have a date selected by the User. I'm trying to find a simple way of working out how many days before that date is a specific "day name". For example if I take the date as 23/12/2019, I want to find out how many days...
  9. W

    Importing multiple excel files into one spreadsheet

    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the previous day's data. To make it more complicated each day's file has two tabs that i need to import but I'm assuming the code below will do this...
  10. E

    Yearfrac problem

    Hi all, I'm using the YEARFRAC function to calculate the fraction of the year worked. Based on 13/5/2019 - 31/07/2019 the function returns <colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody> 0.2166666667. </tbody> However based on...
  11. S

    Calendar day coloring

    Good Morning, I had a few ideas but most of them involved recreating the calendar and I haven't done that yet...anyway A user came to me asking about formatting a sheet he's got. The workbook is a simple "Planner" for work tours: He inputs a day, then inputs the number of days he's going to...
  12. J

    Return Lookup based on range cell

    Hi all. I'm renting out construction tools and have my margin at different rates depending on the number of rental days. This is what my days-margin cells look like: <tbody> D E Days Margin 1 1.4 2 1.3 3-7 1.2 8-14 1.15 </tbody> If I have A3 titled "Number of Days", B3 shows the...
  13. K

    IF ISBLANK formula

    <tbody> Original Revised Days Overdue 12/2/19 31/3/19 X 31/12/19 X </tbody> Good morning all, I wonder if you could help me out with a query. I have a table with two dates, an original date and a revised date. I need the formula which looks at the revised date first and...
  14. S

    Calculate start date

    Hi I am trying to calculate the start date of the first operation based on the start date of the second operation. Typically this is always going to be 7 days (5 work days) before. I can do this easily by just deducting 7 from the second operations start date however I have a calendar lookup...
  15. P

    Average days by type and year

    <tbody> Type Days Year 34 12 2017 34 24 2017 65 11 2017 66 44 2018 34 23 2018 66 13 2018 34 44 2019 65 8 2019 66 31 2019 </tbody> I want the average days by type, by year. So for example, in 2017, the average days for type 34 is 18. The average days for type 65 is 11. I...
  16. J

    Date Difference

    I am sure this can be done, but I am not able to find anything specifically addressing it. Is there a way to create a DateDiff parameter in a query, where is it calculating the days, between current date and a date field within the table the query is built from?
  17. M

    Rounding the months and days in between 2 dates

    I'm calculating number of months and days in between 2 dates (jan1 2019 & Dec 31, 2019) using a formula Value(Datedif(A1,B1,"M")&"."&datedif(a1,b1,"md")+1) and i am getting a result of 11.31 but i wanted to appear as 12 instead (since 31 completes the whole month). I want to get the real...
  18. J

    conditional formatting or formula to change cell color

    I am looking for help to change a row of cells color based on a date. Here is the problem: If a person is to leave a job on a future date (X) and I want the row of cells containing that persons data to change colors based on how close today's date is to that future date. If the future date...
  19. C

    Summerizing Dates

    I have a column of both future and past dates. I would like to create another column that groups the dates into summary classes (ie. Overdue, Due in 7 Days, Due Between 8 - 30 Days, Due Between 31 - 90 Days, Due 91 - 365 Days). Is there a formula that can do this?
  20. H

    Countif with Criteria only visible cells

    Hello Friends, I am using this formula to count cells if they contain the word DELAY in Cell A1 =COUNTIF(A2:A10,"*"&"Delay"&"*") Here is a sample list From A2 to A10 Delay 25 Days On Time Delay 52 Days Early 12 Days On Time Delay 52 Days Early 12 Days On Time Delay 52 Days I want the...

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