Find Day Of Week Of Today And Display Date Of Selected Day?

Mar 9, 2014

I may be making this more complicated than it needs to be but I can't seem to figure it out. The goal of this spreadsheet is for General Managers of a restaurant to give feedback about food deliveries. These deliveries usually come on Monday, Thursday, and Saturday, but could for a list of reasons come on any day of the week. I want to automatically populate as much information as possible without requiring GMs to remember and enter dates.

A1 is =TODAY()
A2:A5 is a drop down where the GM selects the day of the week the delivery arrived. i.e. MON, TUE, WED, etc. I want the corresponding date of the past week to auto-populate in this cell (or even the adjacent cell if necessary) once the day is selected. For example, if today is 3/8/2014, when MON is selected in A2, I want the cell to automatically add "3/3/2014," making the entire cell read "MON 3/3/2014."

So if said GM is filling in this spreadsheet on a Saturday, and I want to display the date of the previous Monday, I need to find the day of the week of A1 (WEEKDAY function), and make A2 = A1-5.

If today is a Friday, it would be A2=A1-4, and so on for the 7 days of the week.

Then I would need to do all of this for if a Tuesday is selected in the drop down box, meaning IF(Saturday)Then A2=A1-4, etc...

I was trying to string together IF statements like this:

=IF(WEEKDAY(TODAY()=7),A1-5), IF(WEEKDAY(TODAY()=6),A1-4)

It works when I do only the first IF statement, but when I add another it returns #VALUE. I thought that excel would find the first true value and stop evaluating.

View 2 Replies


ADVERTISEMENT

Show Week Number Of Today Date From Starting Date

Jun 27, 2014

is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.

View 3 Replies View Related

VBA - Changing Dates According To A Week Before Today Date

Sep 11, 2013

So I have this sheet template to copy for various other sheets, and on A3-A1000, they contain dates from August 1st 2013 to whenever. Starting with A3 as August 1st. The problem is now, it's September 10th, and I would like A3 to be a week before September 10th. Then A4 would be September 11th. And so on and so forth. The dates also must only consist of weekdays and the dates must be hard coded. Is this possible? I have the simple code for copying over the spreadsheet, but I don't know the rest.

Code:
Sub Submit()

Sheets("Client Name").Copy after:=Sheets(Sheets.Count)
ActiveSheet.name = Sheets("Control").Range("C19").Value

View 1 Replies View Related

Countif (?) Within A 2 Week Date Range Of Today

Sep 11, 2003

I have a workbook which contains 1 spreadsheet that contains data entry for approximately 20 employees. The workbook then contains a separate sheet for each employee to display the detailed information

Column A stores the dates from Jan1 to Dec 31
Row 1 contains the employees names.
The data entered consists of approximatle 4 different 1-letter codes as to what transaction occurred that particular day.

What I would like to do now is be able to count the number of cells that contain a code for 2 different time periods. I would like for it to count 2 weeks ago and separately count 2 weeks in the future.

In trying to get this last calculation, I've added a column for WEEKNUM next to the date (column B) and used somethign along the lines of
=CountIF(C2:c366,Weeknum(Now()-2)) and also tried +2. Neither have worked.

View 9 Replies View Related

Finding End Of Week Prior To Today's Date

Aug 15, 2007

I am being supplied with a date (but not weekday) in a report. How do I find the date of the prior Saturday without having the weekday supplied?

View 4 Replies View Related

Using VLOOKUP To Display Date If Present, If Not Display Today's Date

Feb 26, 2009

I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.

Atm, it reads some like this

View 3 Replies View Related

Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

View 9 Replies View Related

List In Combobox When New Item Selected Put Today Date In Adjacent Column

Jan 20, 2014

I have a spread sheet for equipment that gets loaned out on a regular basis. Cells B4 - B25 have combo boxes in them, with the options to select "On Loan", "In Stock Room" etc in them. What I would like to do is create something so that as the combo box is changed, i.e. from "In Stock" to "On Loan", a date changes adjacent to each combo box.

For example, combo box in B10 has been changed from "In Stock" to "On Loan". The adjacent cell records the date automatically that the combo box was changed.

Combo box date example.jpg

View 7 Replies View Related

Find Difference Between Last Value Corresponding Date And Today

May 1, 2014

I am trying to find the difference between two dates.

Column B consists of date (oldest to latest), Column C to Column P consists of data, I have entered formula in Column Q to get value if the conditions are met, else the cells will remain blank (but those cells contains formula)

For eg: =IF(C4395=1,J4395,"")

There are thousands of ROWS in the worksheet.

I need to find last value in the Column Q, corresponding date (assuming if the last value is in Q4395, I want it's corresponding date which is in Column B) and finding the difference between today and that found date. All these put together in one formula.

I managed to get last value in the column by using this formula, but I couldn't get it's corresponding date.

=LOOKUP(9.99E+307,Q2:Q4418)

View 5 Replies View Related

VBA Code For Find Today Date

Mar 5, 2014

My need is if i press one macro button it will select today updated cell. Is there any macro code for find today date?

View 3 Replies View Related

Find Pay Date From Date Sold (Friday Of Following Week)

Feb 17, 2014

If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.

View 3 Replies View Related

Formula To Find Date Closest To Today?

Apr 9, 2014

I cells B5:B15 I have a list of dates from last year. I need a formula that I can put in cell D5 that gives me the date that is closest to the same day last year.

Example:

If today is 4/9/14

and

From B5:B15 there were the following dates:
3/12/13
3/19/13
3/29/13
4/5/13
4/8/13
4/12/13
5/6/13
5/29/13

Cell D5 would contain

4/8/13 since it is the date closet to same day last year.

View 4 Replies View Related

Find The Last Date Of The Week

Jan 20, 2007

I need a macro to find the date at the end of the week.

Ie today is saturday the 20th january - Macro would return the value 21/01/07.

Or IF the date today was the 24/01/07 - Macro would return the value 28/01/07.

View 9 Replies View Related

Find Closest Date To Today For Series Of Data

Jun 23, 2014

I have the following table of stocks with corproate action types and dates with a ticker / identifier per stocA1:

Type
B1:
ID
C1:
Date

[Code]....

I am trying to find the NEXT Dividendcorporate action that is CLOSEST to the current date. I've done some google trawling and found a few formulae that seem to work only if the identifiers or dates are in a particular order. ALso having trouble using TWO criteria

View 1 Replies View Related

Find Week Based On Date

Apr 17, 2014

I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :

1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number

I've seen a solution using the Median function but I'm not here it would work in my case. link here

I've attached a workbook : Date_Sample.xlsx‎

View 12 Replies View Related

Vba To Find The Month And Week Number From Given Date

Apr 22, 2009

In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)

The Week ( Monday to sunday) which need to be calculated is the week no. in the given month

like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april

View 14 Replies View Related

Find Date Of Weekday In Previous Week.

Sep 27, 2009

I've been racking my head on this one.

I need a function where for a given date will return the date of a specified weekday in the previous week.

Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)

View 9 Replies View Related

Excel 2010 :: Find Months And Days Between Today And Another Date That Is Result Of Function?

Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is H22.

I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

I should also probably note that the date in H22 is the result of another function.

=EDATE(G22,12)

View 5 Replies View Related

Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today

Apr 4, 2008

how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.

I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.

CREATE TABLES LIKE BELOW?Column A Column B Column C

March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................

View 4 Replies View Related

Formula To Count Value In Column That Was One Week Ago From Today?

Apr 28, 2014

I have 2 columns in my spreadsheet:

B:B is a column of dates.
C:C is a list of names

formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days

View 2 Replies View Related

Finding All Dates In A Column That Are In The Range Today To A Week From Now

Sep 21, 2008

Im trying to search a column (A), that has a list of dates (not in order), for the row in which the dates are equal to or greater than today and less than or equal to a week from today. I then want the information contained in the rows with these dates to be transferred to another sheet and ordered by date.

View 13 Replies View Related

Find Certain Cell That Contains Today Date - Select Cell And Its Sheet

May 8, 2014

I have made the macro that does as ,soon as i open the file, select today's date but only if date is in sheet5... sheet 5 is may so it works. Doing the same job for every sheet in the file.

View 10 Replies View Related

Adjust Formula To Show A Selected Day Of Week?

Feb 25, 2014

I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.

=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))

Is there any way to adjust it to do this the drop down box for the day will be in F9.

View 3 Replies View Related

Sort Table In Date Order But With The Date Nearest To Today's Date At The Top

Nov 19, 2009

I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...

The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...

I use xl 2003.

View 9 Replies View Related

Display Week Ranges

Dec 8, 2008

I'm a rookie and looking for a simple way to display week date ranges as shown in the attachment for a year at a time.

View 11 Replies View Related

Display Day Of Week From Dates

Mar 15, 2008

I have a column of dates in one column and in the next column next to the dates, I want to show the day of the week that each date represents, such as;

Col A Col B
1 March 2008 Sunday or Sun

etc etc..

View 5 Replies View Related

Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41
Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

View 3 Replies View Related

Prefill Text Box In Userform With Today Date But Allow User To Enter Unique Date

May 29, 2014

I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.

[Code] .....

Attached File : Staffing Report 1.90.xlsm‎

View 7 Replies View Related

Date Countdown: Results Of The Number Of Days Between Today And A Future Date

Apr 8, 2009

I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?

View 3 Replies View Related

Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date

May 19, 2009

I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:

5/10/2009 to today is -9

5/22/2009 to today is 3

View 2 Replies View Related







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