Determining Date Based On Other Date

Mar 9, 2013

So i have have a two week period that is established by two dates (a start date "F3"... And an end date "F4")

I have a formula that determine if the "1st" of the month falls within the 13 days preceding the start day. This will notify me that I can the deposit my tenant first check on the start date.

Now that that is known, i would like to create a formula that will establish a second date in that month that will notify me when I can deposit my other renters payment. It will always be two after i deposit the first renters cheque.

View 2 Replies


ADVERTISEMENT

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

Determining If Date In Row Is Within 90 Days Of Date Accessed?

Jul 27, 2012

ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed

View 2 Replies View Related

Determining Date Ranges

Mar 31, 2009

I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.

I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.

Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?

View 4 Replies View Related

Determining Date Of 2nd Or Last Friday Of Any Month?

Sep 14, 2008

Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?

View 14 Replies View Related

Determining If Datetime Falls Within Start And End Date Times

Sep 25, 2013

I have been using this statement

if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))

And it just works for the first 2 values c1, c2 and doesn't fit for the others.

The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.

Here are some samples:

Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02

So as you can see there are more than one event between one start and end dtimes.

View 4 Replies View Related

Determining If Datetime Falls Within Start And End Date Times

Sep 25, 2013

I have been using this statement.

if(and(c1>=a1:a144,c1

View 3 Replies View Related

Calculate Next Due Date Based On Start Date Frequency And Current Date?

Mar 9, 2014

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)

View 4 Replies View Related

Date Fields Increase Date By Numbers Of Years Based On Lookup Table?

Aug 11, 2013

In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -

d1 e1
Restricted 5
etc

Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017

What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.

View 1 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

Count Unique Instances Of Date While Filtering Based Upon Date And ID Number

Aug 14, 2014

I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.

I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.

I've attached an example work book and I would like the result in H2.

Example Workbook.xlsx‎

View 5 Replies View Related

Fill Date Across Columns Based On User Date Range Input?

Mar 5, 2014

based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"

I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.

Code:

Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....

View 1 Replies View Related

Compare Dates To Date Range & Return Date Based On Outcome

Jul 1, 2008

I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.

To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet

View 2 Replies View Related

Find Corresponding Date Range Based On Single Date And ID Number

Mar 28, 2014

I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.

I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8

Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014

What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1

In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014

View 13 Replies View Related

Selecting Data From A Date Range Based On The Current Date

Jun 20, 2013

I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:

09-15 16-22 23-29

with the month manually put in above it.

then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.

Budget Picture.jpg

View 12 Replies View Related

Sum Year To Date Forecast Based On Row And Date Criteria?

Feb 25, 2014

I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).

View 2 Replies View Related

Searching For Next Largest Date Value Based On Current Date

May 30, 2014

Future Date Sample.xlsx

I have attached a spreadsheet with a formula I'm looking for. I tried HLOOKUP, INDEX & MATCH, And others, but I was nowhere near what I needed.

View 2 Replies View Related

Formula To Enter A Date In One Column Based On Date In Another?

Jun 10, 2013

is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.

View 7 Replies View Related

Ship Date Based On Current Systems Date

Dec 14, 2011

I have a report that in column A shows a ship date. These ship dates may be "Previous","Same Day", "Next Day", or "Future".

I need VBA code that will look at the day in column A and determine if it matches the current date on the computer if it does then in column W in the same row I need the string "Same Day".

If its the systems date plus 1 day column W in the same row would state "Next day".

If its the systems date plus 2 or more column W in the same row would state "Future".

If its any date prior than than the current system date then column W in the same row would state "Previous".

View 9 Replies View Related

Formula For Displaying Next Quarter Date Based On A Date

Mar 22, 2012

What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.

Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.

The formula I have got to work is

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)

but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER

when I change the formula to

=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)

The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?

View 6 Replies View Related

Fill Table Based On Another Within Date Range & Cut Off Date

Dec 31, 2009

I have an 'existing results table' as per my attached sample.

I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.

I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.

Please can someone show me the formula on my attached sample?

Please see my example in red.

I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.

View 8 Replies View Related

Number Of Days To Add To Date Based On Weekday Of Date

Apr 23, 2008

I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008

View 4 Replies View Related

Text Based On Date Being Year From Current Date

Apr 29, 2008

I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!

Cell A1 contains a Date eg (01/01/07)

In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.

View 3 Replies View Related

How To Get Last Friday Date Based On Current Date

Feb 14, 2013

I need to work out how to get the last (or most recent) Friday date. Suppose today is 14/02/2013, then the previous date is: 08/02/2013. If today is 15/02/2013, then I just want it to be 15/02/2013 as it is Friday.

View 3 Replies View Related

Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays

Nov 15, 2008

First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...

View 7 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

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.

I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).

When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)

So my questions are:

1. Is this due to the formatting?

2. Is there a way to change the date format supplied by the date picker?

View 7 Replies View Related

How To Auto-Populate Date Range (working Days Only) From Start Date And End Date

May 25, 2014

I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 1 Replies View Related

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)

Dec 8, 2009

I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

View 6 Replies View Related

Date Range: Reference A Single Date And Output Date Ranges

Oct 11, 2008

I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc. It should look like:

Monday Date:
10/13/08
October 19 October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as Ill have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View 2 Replies View Related







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