I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.
ie: start date: 26/12/13 -- End date: 25/06/14
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
I have about 80,000 dates. What I need to do is to place an x by the date if it falls inbetween January. For example, I have start Date in A1 and End date in B1. If the start date was 10/20/2013 and the end date was 6/11/2014 then that woub give me an "x" because it went through January. If the start date was 11/23/2013 and the end date was 1/1/2014 that would give me an "x" because it fell in January but if I had 8/4/2013 as a start date and 12/29/2013 then that would be blank. Hope that I made myself clear enough.
View 2 Replies View RelatedI needs a formula (not VBA) to calculate the required start date.
I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00
My working week is from monday u/i friday and every day I work from 08:00-16:00
At what time do I have to start the job to get it done in time.
The formula should give this result: 01-may-2014 15:00
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
View 5 Replies View Relatedif I've worked in the company for 9.0384 years, how can I calculate that my start day was 04/20/04?
View 3 Replies View RelatedAs everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?
For example,
Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009
Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days
The Start Date should be 30 May 2009.
Seems like I am not able to use WORKDAY function to calculate the start date.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I have a small project at work where I am being asked to put a simple spread sheet that will calculate a start/stop time - and also include the date. For example:
Start time 5pm, date: 3/13. The spread sheet to auto calculate what the stop time and date will be if a specific amount of hours is to be calculated. For example in this case, 12 hours. From calculating in my head that would be 5am the following day. However, how can I get this in excel to work and therefore all i would have to do is enter the start time and date, + 12 hrs, and excel would calculate the time/date after the additional 12 hrs.
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.
It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View 2 Replies View RelatedI'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.
View 1 Replies View RelatedI have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.
For example:
I have a workbook with two worksheets in it.
The first worksheet is the input page.
On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.
Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12
Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)
(in this example, this should be 3) and displayed as 03-Sep-12
Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1
The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.
The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.
Daily
Date
USD
29 June 2012
1.0191
1 August 2012
1.0507
[Code] ........
Worksheet 1, Cell C1 should display the value of 1.0266
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.
There is of course several ways to do this, but is there a simple and easy formula one can use.
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?
I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08
I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09
I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:
Code:
=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))
The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:
[URL]
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.
I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.
where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.
I would like a formula that will calculate these previous month end dates correctly.
See example below ....
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
I have a spreadsheet and every row has an incident date. What I would like to do is calculate which relative month it is compared with today's date. I know how to show if it is in the current month, but what I want is something like the following:
Today's date is 9 July 13
Row 1 date = 9 June 13, hence calculation = -1
Row 2 date = 17 May 13, hence calculation = -2
Row 3 date = 29 Jan 13, hence calculation = -6
etc
Some sort of month to month comparison resulting in an integer.
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
i have a report that needs to be filled out with total purchases daily that keeps a running total. So each day i have a column with a new figure. Looks like this:
Date Purcahses Total
30/01/09 10 10
31/01/09 10 20
01/02/09 10 30
02/02/09 10 40
etc
On the 20th of each month, i want to create a macro that wipes out the historical data prior to 1st of that month. i.e. on Feb 20, i want to lose all the january rows.
I want to put "Jan" (as in January) in a cell on Sheet1 and in cell A1. In cell A2 I want to dispay the start of the month and in cell A3 the end. So if I change A1 to "Feb" or any other month it will display the correct start and end dates.
I then want to use either the month or the dates to do a SUMIF statement. It a simple one like -
=IF(C5="","",SUMIF(April!R:R,"="&C5,April!G:G))
But I don't want April in the formula as I just want to update the cell in A1 to read in the data for a different month. BTW, April is on another sheet along with a sheet for each month.
I have a PM (Preventive Maintenance) spreadsheet which tracks the progress on all our maintenance workorders providing a % of PM's complete each month. It has been manually updated each month, but I want to have Excel remember the percentage for each given month and record it in a seperate cell. I.E. D100 tells me PM compliance is 97% MTD for September, but when October 1 arrives I have to remember to take the number as it was on the last day of the month and record it somewhere else. I want to have excel automatically save the number (%) as it is on the last day of each month and keep it. I have used a simple IF function ( IF(C90>09/01/2007,D100,"") to get the cell to record the number during the active month but I lose it as soon as the next month starts.
Is there any way to have excel recognize the new month then save that number in another cell when the next month begins?