# days

1. ### 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
2. ### 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...
3. ### 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...
4. ### 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...
5. ### 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...
6. ### 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...
7. ### 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...
8. ### 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...
9. ### 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...
10. ### 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?
11. ### 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...
12. ### 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...
13. ### 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?
14. ### 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...
15. ### Count Days Between Two Dates

I thought this would be simple but my attempts are not working for me. I need two things PLEASE. I am trying to calculate the total number of "days" between two dates, and secondly, calculate the number of "week days" between the same dates. Each would be in a separate cell. I am totally...
16. ### How to count a number of consecutive cells with 1 in row that is intermittently interrupted with blank

Good day all, My excel skills are very poor. I have a situation where I want compute the average number of times a student attends class consecutively over a period of say a semester. Rows represent individual students and the columns are days. In the cells are either 1 if students attends or...
17. ### conditional formatting

Hi I have the table below. I want to use conditional formatting or any other way to highlight the event based on condition like the following: If the event takes less than 14 days, then I highlight "Current" in the 3rd column. but not to highlight the "xmas" or any other events which have...
18. ### Excel Newbie

Hi everyone, Im trying to go above and beyond by making an inventory sheet for work. I want excel to highlight the expiration dates of items that will expire in less than 30 days but I have no idea to write the formula(s). If I write out what I want to have happen it looks something like this...
19. ### Count left working days in current month

Good day everybody, I am facing a relatively trivial problem, but somehow I can not continue. Aussprache lernen I would like to know how (dax measure) many working days are still available in the current business month from today (in Excel/ Power Pivot). I have the following table...
20. ### Date ranges in excel

Hello, I have a data set bunch of work dates for a list of employees in 2018, with a breakdown of what's billable and what's non-billable days. I also have travel reports to indicate date ranges that they were in London. Is it possible to figure out of those days they were in London, how...