# date calculation

1. ### Date Calculations

Hi Clever Excel World, I have a sales sheet which in one column I have dates (the sales were made). When the sale has been processed and ordered we take this off the sheet but sometimes sales remain on there for a length of time. in another column I need to calculate the days that its been...
2. ### Conditional Formatting issue beginning with CY 2020 dates

Hello, I have a large spreadsheet that I use to find patients that have had a procedure outside of their admission and discharge dates. The spreadsheet has worked perfectly until 1/1/2020. The conditional formatting formula I am using is: Cell Value ---> not between ---> J7 --->...
3. ### Date strategies in calculations

I am trying to work out a system of "Actual" versus "Budget" based on today's date. I have a column per month of expenses and at the bottom, it is totalled. Below that is a budget for that month for the entire column. As each day of the month progresses, I want the next cell down to show the %...
4. ### bi-weekly occurrence within given range

Hi, I am setting up a family budget planner, and want to list the fixed outgoings (Description / Amount / Frequency [Monthly / Weekly / Bi-Weekly] / First Date Taken. The variable outgoings, I want to list in a separate table (Date / Description / Amount). The problem I am having is as...
5. ### Days until the next date

Hello All: I need some code. I have 40 cells that may/may not contain dates. Range is H19:H38 and I19:I38. Here is my need via example. If H19 has a date and I19 is empty, I need to know how many days (based on the current date) until the date in H20. If H19 and I19 have dates and If H20...
6. ### Payables Cash Requieents

Cash requirement for an Accounts Payables environment. Link provided for sample data set with desired date results highlighted. https://1drv.ms/x/s!Auu67iC5u960_VhNg5EF3ywaGrCe To provide an accurate detailed daily cash required forecast for payables, invoices and credits must be calculated to...
7. ### Date calculations formula

I have 2 dates i.e. 5-Dec-16 in cell A1 & 10-Nov-17 in cell B1. Is there a way to calculate the number of days broken out relative to each year? Thanks
8. ### Adding and subtracting time and date within a specified date range

Hi, I need to create a dynamic table that helps with backplanning from a specified date and time. It's meant to be used for project management, so I essentially something like this: If someone enters data into the blue boxes the full table should populate with dates and times in the format...
9. ### Need Formula to Stop After Certain Date is Reached

I am running a pref calc on a property with multiple investors and the pref % changes each year. However it is not compounding so no pref is earned on pref from the previous year. How do I stop each formula once 365 days have passed and then begin with the new pref for the next year?
10. ### Incrementing Cell Address Numbers by Formula

I am trying to create a formula that will populate a non sequential set of cells in a column with data from another column that is sequential. I can't seem to figure out how to do it. For example: I have in one column that is a textual form of the date: Column B <tbody> August 31, 2016...
11. ### Conditional Formatting 'greater than/less than' for cell containing variable date based on a formula

Please please please someone help! I have a spreadsheet as below. A2 - 'Date Approved' is manually entered, B2 - 'Review Date' and C2 - 'Trial Expiry' are automatically calculated based on the date entered in A2 - 'Date Approved'. I need to conditionally format as follows: B2 - Cell to turn...
12. ### Automatic date calculations

Below is a list of bills, I'm trying to figure out a way to have the Due Date column automatically calculate each month after the date is passed i.e. today is October 30th, so every due date before that will automatically show the dues date in NOV. Like bill # 1 is due the first of this month...
13. ### Calendarize invoice data into standard calendar months

Hello, I'm working with a large number of invoices that do not follow standard calendar dates (i.e. the first day of the month to the last day - 8/1/13-8/31/13). I'm trying to find a systematic way to convert these invoices into standard calendar dates so I can evaluate various locations...
14. ### VBA Array - Help with Date Calculation

Hi All, I am a newbie to VBA programming and I am very much interested to correct my mistakes However, I am unable to solve a problem that involves calculation of Weekdays and storing them in an array Excel Version: 2007 Problem : Column A Column B Column C 16-May-13 19-May-13 In the...
15. ### Date Calculations

Hello, Can someone help me with this. I have a spreadsheet to track vehicle buyouts, each vehicle has a row and the columns would look something like this; Vehicle Number, Start Date, End Date, Monthly Amount, Term 00698 - Ford, 1-Sep-10...
16. ### Changing Text Format with Formula or Conditional Formatting with multiple values

Hello all! To give you an idea of what I am trying to do, let me break down what I have... I currently have a workbook with multiple spreadsheets used to track requests received, and follow them throughout their life... sort of a "help desk" type of tracker, where we assign a ticket number and...
17. ### Complex Date Calculation Help

I am wanting to create a school calendar that calculates student hours based on one of six schedules, omitting weekends and given holidays. The schedules might be: M - F all day (5 hours per day) M - F a.m. (2.5 hours per day) M - F p.m. (2.5 hours per day) MWF (5 hours per day) MWF a.m. (2.5...
18. ### Date calculation and rounding within a fiscal year

First of all: best wishes to all the geniussus (i.e. you, yes you know who you are). I was wondering if any of you has had this challenge before. I'm trying to do a date calculation in a fiscal year. I have a date (1-26-2010) and want to add a number of days (90) I then want to round down to...
19. ### datedif count down before or after today

Hi, I have a list of service dates the can be either a future date or a past date? I want to reference the today function and look at the list of dates and tell me how many months have passed between the two dates.i would like it to show say 30 months to go till next service or -30 because it...
20. ### Set day and month on date column formula

Hi, I have a date-time column [CreatedOn] e.g. 11/08/2010 11:31. I need a formula to extract the year e.g 2010 and then set the day to 01 and the month to 06 and the time to 00:00 and return this as a date-time value. The formula might need to be two parts extract the year from the CreatedOn...

### 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 MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

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 "mrexcel.com".
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