# Formula To Obtain Current Week Monday Date

Mar 17, 2013The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.

View 3 RepliesThe portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.

View 3 RepliesADVERTISEMENT

Can weeknum work with the week starting on saturday instead of sunday or monday?

if not, what are my options?

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

Code:

ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _

xlFilterValues, Criteria2:=Array(1, "3/10/2014")

Range("A59").Select

ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"

Range("A60").Select

I am currently using this formula =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) to get me 6 months ahead from a certain date. There is a new problem that has arose for me. I need a formula that will give me 6 months from a certain date if it falls on a Monday if the 6 months does not fall on a Monday then give me the next Monday date.

for example 6 months from 1/2/2007 is 7/2/2007 which falls on a Monday which is fine but, If the start date was 1/3/2007 this falls on a Tuesday so I would need the next Monday date 7/9/2007.

I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.

"Available" means an employee has NO task at all.

Is there a snatch of macro that can assign two variables with the date for the beginning and ending date of the current week?

I need

A= 4-19-2009 12:00AM

and

B=4-25-2009 11:59 PM

Based on the current week being week 16, with the variables changing as the weeks progress.

Outlook allows you to easily specify, but eExcel always treats Sunday as the first day of the week by default. Yes you can manipulate that within formulas and functions, but if you use the autofilter in excel and choose "this week" (or "next week", or "last week") excel considers Sunday as the first day of the week. I would like to know how (or if) this can be changed to Monday as the first day of the week.

View 5 Replies View RelatedI have seen this before and once figured it out, but I have not had any luck this time. I'm looking for a formula that will reference a cell that contains a date and will then return the date of the Monday of the given date's week. If I gave it a cell that contained 2/15/07 it would return 2/12/07. Probably a simple question but I think I must not be working on all cylinders. =)

View 6 Replies View RelatedI have a formula

=AL260-WEEKDAY(AL260,2)+MATCH(AP260,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)

The formula looks at the chosen start date (AL260), and then uses a displayed text day (AP20) to display the relvent date to the text day, this works fine however if the start date is say 08/08/14 (a Friday) when the formula detects a Monday (this copies down a range looking at the start date and the text days to produce dates on each line, the formula displays the Monday dates prior to start date, is there any way to alter this to make it display the dates ahead of the chosen start date?

i got a problem with date range.actually i wanna to insert date range automatically which is referring current week.

View 2 Replies View RelatedThis formula on the forum has helped me to find the first day I specify of the month of a certain year. (here a monday in august of the year specified in B1)

=DATE(B1;8;8-WEEKDAY(DATE(B1;8;1);3)-IF(WEEKDAY(DATE(B1;8;1);3)=0;7;0))

As I'm not really good at understanding the formula (I get confused with the =0;7;0 bit), even though I can make it work I'd like to know how I can find the a last specific day (mo-su) in a month.

I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.

View 3 Replies View RelatedI uploaded an example file.

Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.

The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).

So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).

Is there a formula to return the value of week from 9-6-09 i have on cell a1 and 1-2-10 on cell a2 and i want the total number of weeks on a3.

View 3 Replies View RelatedI 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"......................................

This might be a simple date transformation formula that I need

Column A has numbers like: 200517, 200530, 200544, 201036, 201043, etc

I'd like to get a formula in Col B in corresponding rows that would show date as dd/mm/yy

I know the numbers above are yyyyww = yyyy is year, and ww is the week of that year.

So the formula would have to convert that particular week to the day and month. I understand that are 7 days in a week, but if the formula would reflect Monday of that week, it would suffice.

The clue's in the title. I have a date in cell (BC25). I need to jump forward by one year then if the date doesn't fall on a Monday I need to find the next Monday.

I've looked at this thread which should point the way but I'm particularly dim this morning and can't get it to work.

http://www.mrexcel.com/forum/showthr...ghlight=monday

I need a VBA code which tells me the date of previous monday of any date. The user types in any date to the cell A1, and I need the date of previous monday to be inserted to the cell A2.

For example:

A1: 9/5/2009 --> A2: 4/5/2009

A1: 19/5/2009 --> A2: 18/5/2009

A1: 27/12/2009 --> A2: 21/12/2009

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:

1st Table

FROM TO WK

3/27/2009 4/2/200914

4/3/2009 4/9/200915

4/10/2009 4/16/200916

4/17/2009 4/23/200917

4/24/2009 4/30/200918

2nd Table

DATE

03/28/2009

04/11/2009

04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

I've been trying to get this to function correctly. The following code does pull out a list of all filepaths, but what I want to to is access the properties of the file.

Basically I select a customer from a combo box, and from this the code does a vlookup to determine what folder the reports are stored in. The list is then entered onto the worksheet 'FilesInReportFolder'. There is some commented out code as I've ben trying to come up with a simple way to remove the 'xls' from the end of the string, but din't worry about that.

I've found a website that suggests adding .name to the lineto get the name instead of the path, but what I really want is be able to pull out other details about the file.

Here's my

Private Sub ComboBox1_Change()

Combobox1value = ComboBox1.Value

Dim rng As Range

Dim RowNo As Integer

Dim NoOfFiles As Integer

Dim foundfilepath As Integer

Dim FilePath As String

I have the following data:

Column A = Date

Column B = Reservations made per day

For ex:

A B

1 3/1/2011 5

2 4/5/2011 10

3 3/8/2011 15

Then I have a look up table where based on the date ranges it assigns a week number.

WeekDATE Range 1Date Range 2

718-Feb-1124-Feb-11

825-Feb-1103-Mar-11

904-Mar-1110-Mar-11

1011-Mar-1117-Mar-11

1118-Mar-1124-Mar-11

1225-Mar-1131-Mar-11

1301-Apr-1107-Apr-11

1408-Apr-1114-Apr-11

1515-Apr-1121-Apr-11

1622-Apr-1128-Apr-11

I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.

Is it possible to to return a date that begins on following mondayand adds days unless it was entered on a monday then just add 17 days

if someone enters a date say 10/01/07 (wednesday) i need to be able get the following mondays date and add 10 days to it IE 25/01/07

to determine the number of 1st Mondays or any weeday e.g. 5th Fridays between a date range e.g. 1/1/2006 - 4/1/2006.

I've noticed a lot of threads for calculating just one month but none for a period of time.

I want to be able to test if a date entered in a cell (Format dd/mm/yyyy) equals a "Monday". If it doesn't, I want to display a warning message.

View 6 Replies View RelatedI am creating an IF formula that will produce the current date in one cell if another item has been entered in another cell.

For example

cell g (accounts) is blank, cell H (date found).

Once the correct account is entered into cell g the date will autofill in H.

The formula i was using is =IF(G1"",TODAY(),"")

Currently having problems getting correct head count. I have formula that works for rows 6-8 but fails in row9. The should be answers are in rows 17-20.

Conditions used in formula

*Start date > Start FY =0

* Current Week > End Date =0

* End Date < Start FY =0

The date difference is divided by 7 because there are 7 working days in a week. If it is greater then 7 then it would be 1 for current week. I tried zipping the file but I could not shrink it to required size. find on weblink below: http://maxupload.com/E759C9D9

I'm trying to find the calculation which will automatically have the date for the first Monday in January to show

e.g. Cell A1 input 2010 Cell A2 would show 04/01/2010

Cell A1 input 2011 Cell A2 would show 03/01/2011

etc

how i can fix the formula below to show the number of remaining days between the given date and the current date.

View 3 Replies View Relatedhow it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,

View 9 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved