Round Up To A Specific Date

Dec 2, 2009

I have run into another block wall.

Here's my problem:

I need to know if there is a formula that will allow me to not only calculate a date from two different cells -- but also to round the answer to THE NEXT 15TH OF THE MONTH beyond that date.

Ex: Cell F5 has a purchase date. (1/1/10)
Cell F25 indicates a specific number of months. (3)
Cell F5 calculates the totals of F5 plus the # of mos. in F25. (4/1/10)
But what I need to do - is to have Cell F5 show 4/15/10. (or, if Cell F5 were to come out to be 4/16/10 due to different figures in F5 and F25 -- it would need to indicate 5/15/10, and so on.)

View 9 Replies


ADVERTISEMENT

Round Off A Date

Jan 27, 2010

How do I take a date value and round it off to a fixed month and day of the year following the year entered.

Ex: In cell A1 I have a date: 12/28/2009

I want to round off the date to January 1 of the next year. So I need a function that would return 01/01/2010 (jan 1 2010).

View 5 Replies View Related

Round Up Date..

Oct 8, 2008

When a new employee gets a uniform, after they write their first order, I have to make sure the cost of the unifrom gets deducted from their check. Payroll ends on Sunday every week. I want to use conditional formatting to highlight the cell when it's eligible for deduction/reimbursement. No matter what day they write the order, it won't be eligile until after Sunday. Because the day of the week will vary, what type of formula can I use to highlight the cell? My sale dates are in column "AC" and the reimbursement column is "AF".

View 4 Replies View Related

Round Date Based On Day Of Date

Oct 17, 2006

I am trying to make a spreadsheet that will calculate how long someone has been working for us but what i need is for the date started to be rounded to the start of the nearest month. (ie. Start Date: Jan. 1 - 15, = Jan 1. and Start Date: Jan.15 - Feb.15 = Feb. 1....). Is there a way to do this? I figure i may have to use some sort of table but i dont know which one to use or how to use it.

View 7 Replies View Related

SUM By Both A Specific Month, And A Specific Year From A Single Date Field

Nov 30, 2007

So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}

But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}

SUM by both a specific month, and a specific year from a single date field?

View 3 Replies View Related

Date Wise / Name Wise Total With Round Function?

Aug 3, 2014

i have a large data in excel sheet A2:C1500 i have attached a sample sheet for it, i want in cell C27 round off sum (as like in cell D27)

View 3 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related

Formula For Determining If Two Date Columns Fall Within Specific Date Range

Apr 21, 2006

Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.

If:

A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)

View 4 Replies View Related

Searching For Specific Value On Specific Date

Mar 24, 2014

I'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.

After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.

View 1 Replies View Related

How To Keep Date Entry TODAY Frozen On That Specific Date

Mar 6, 2014

I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:

=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.

So, keep cell A clear untill something is written in cell B. This works fine.

But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.

View 5 Replies View Related

Date/Month :: Specific Date Falls In?

Feb 26, 2008

I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?

View 9 Replies View Related

Calculate End Date Using Start Date And Number Of Days Excluding Specific Days

Nov 22, 2013

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] ...........

View 7 Replies View Related

VBA Round Function Vs Worksheet Round Function

Oct 24, 2006

I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??

View 9 Replies View Related

Changes After A Specific Date.

Sep 10, 2007

I am trying to have cells display red for information that was typed after a certain date. I know that is easy except that I don't wan't the information that already exhists in the cells to be formatted differently, only changes after the date.

View 9 Replies View Related

Sum Data Up To A Specific Date?

Mar 6, 2014

Is there a way to sum data up to a specific date?

I have a large data table, which includes various values throughout the year, if i want to look at the total value up until say 31/01/2014, but my data goes up to 28/02/2014, is there way to do this within the sumifs function?

View 3 Replies View Related

Sum Values From Specific Date

Jan 25, 2010

I tried this (attached File) formula but can't make it work. Formula in F17

View 2 Replies View Related

Add Days To A Specific Date

Jul 11, 2007

A1=10 July 2007
A2=12 (number of days)
A3=21 July 2007

I need a formula in A3 that will basically add the number of days in A2 to the given date in A1.

Note: Don't know how would this work with 30 and/or 31 days months??

View 9 Replies View Related

Indicate Specific Date On Calendar

Aug 10, 2007

Donwloaded a yearly calendar from the Vortex website. Wanted to know if you can have a list of dates highlighted on this calendar.

I have tried the countif function as well as various code from this forum but to no avail.

View 9 Replies View Related

Average On A Specific Date

Jan 8, 2008

in the attached excel file, in cell D21, i have tried to join my SumIF/CountIF formula together with my 2 date range vlookups.

the first vlookup is for the start of november and the 2nd vlookup is for the end of november.

in essence, i want to the average price of all 6160 printers sold in November 07 period.

i cant get the formula working, so any help would be much appreciated by me.

View 7 Replies View Related

Exporting Addresses For Specific Date?

Jan 15, 2013

I have list of many addresses. Then I have different dates related to addresses witch I insert myself regarding colaboration with specific person on that address. Each week I have to make list of all addresses that have the same date. Since manualy this is quite hard to do, I was wondering if I could do it automaticly.

I am attaching sample of what I am talking about. exporting addresses template.xlsx

View 2 Replies View Related

Date Range Need To Have Specific Value Returned

Jan 7, 2014

Every week I have a set of open tickets with various dates. I need to break these Dates down to certain ranges.

Current DateAged Date Range Value to assign
10/20/2013 10/18/13< = 3 days A1
10/20/2013 10/7/13> 3 days & < 2 weeksA2
10/20/2013 9/23/132 weeks - 4 weeksA3
10/20/2013 9/22/13> 4weeks A4

I need in cell H:2 a value returned of with A1 thru A4 based on the results of the information in Columns A:D.

So basically it shoudl have in column H a bunch that fall in the reange of 3 day or less with "A1" then the dates that fall in the range of greater then 3 days but less then 2 weeks a value of "A2", etc. I hope i made this clear enuf to understand.

View 6 Replies View Related

Replace Date With Value From Specific Cell

Feb 28, 2014

If a cell has a date in it (date payment received) I would like to replace it with the value of the payment received that is given in a different cell - how do I do it ?

View 2 Replies View Related

Convert From A Specific Date Format

Jan 7, 2009

If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View 4 Replies View Related

Specific Date Format In Excel

Mar 11, 2014

Is there any way to format the date to DD/MM/YY, and user should be restricted in entering any other formats other than DDMMYY

View 3 Replies View Related

VBA Less Than Specific Date But No Greater Than 28 Days

Jun 19, 2014

I'm using the code below to extract information from a 'Source' (Remaining Allocation Data) to a 'Destination' (All CTO Expired Assignments) sheet.

[Code] ......

The code works except for this part of the script

[Code] ....

What I'm trying to do is as follows:

Extract records where the FinDate is less than the CurrentDate, (i.e. 19/06/2014) and,

Is equal to and no greater than 28 days prior to the Current Date (i.e 22/05/14)

I've tried all sorts of formulas to try and get this to work including the 'DateAdd' function, but I've so far been unsuccessful.

View 2 Replies View Related

Save With Date In A Specific Folder

Jun 16, 2009

I have found out how I save my document as workbook name and date, but now I want to add the saved file to a specific folder instead of just in my dokument folder. This is what I have done so far (SAVING FILE IN A SPECIFIC FOLDER):

View 5 Replies View Related

Save As Specific Name Plus Todays Date

Jul 2, 2009

So i have this macro that i been using for long time everthing works as expected but i will like to change the saving part to save as specific name plus todays date

Sub EQUIPMENT_RETURNS(control As IRibbonControl)
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:J").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select...........................

View 6 Replies View Related

Colouring Cell If It Has A Specific Date

Feb 17, 2010

I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month?

View 5 Replies View Related

Frequency Of Specific Value Based On Date

Dec 19, 2011

how many times "-1" appears in column AM for dates (column B) that are 09/14/2011 or later?

Column B = dates
Column AM = 1, -1, or 2

View 2 Replies View Related

VBA Open Workbook On Specific Date?

Dec 28, 2011

This is a shot in the dark. I have a workbook with 12 sheets, one for each month of the year. Each month is divided into the different weeks of the month. For example sheet January is divided into:

Week 1: 1/1/12 through 1/7/12
Week 2: 1/8/12 through 1/14/12

and so on:

I would like my workbook to open on the cell containing the date range for that particular date.

For example, if I open the workbook on 1/10/12 I need the workbook to open on the cell range that contains Week 2: 1/8/12 through 1/14/12.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved