Revert To Week Commencing

Apr 16, 2014

If my week commences on Saturday 16th Feb (A2), but I do some training on 22nd February (B2), how do I C2 to show the first day of theweek it commenced.

Show in Cell

16th Feb
22nd Feb
16th Feb

23rd Feb
25th Feb
23rd Feb

View 4 Replies


Split Name Into Two Variables And Revert Back To Same Name

Aug 10, 2012

I have a query where the correct name is mixed up by each single click of a button and finally the mixed characters reverts back to correct name. I will give the examples below.

1) Beautiful --> next click of a button
2) utifulBea --> next click of a button
3) Beautiful --> the final output will be back to same name.

View 3 Replies View Related

Revert (Bold) Underline Back To Normal

Jan 12, 2014

What I mean by my "bold" underlines:


Notice the difference between the bold underline and the normal ones. There is also some weird thing happening where in some cells, when I use the underline feature, it shows as a bold underline, but once I've pressed enter to move on to another cell, the underline reverts back to normal. If I select to edit (F2 or double-clicking) said cell, the underline becomes bold, and vice versa (s3lw.png).

All this started happening immediately after I did this (was just experimenting, and I have no idea what this actually does, or if it was the cause of those occurences): Insert > Symbol > Special Characters > No-width optional break. I did that to just one cell though, and now all this weird stuff is happening.

View 4 Replies View Related

Cells Revert Back To Letters Instead Of A Symbol

Sep 7, 2009

The code brings up the message box the way I need it to

At the bottom of the code in the standard module is what I changed.
In the message box. If the GDT button is clicked the GDT chart opens. If the Symbol button is clicked the Symbol chart opens. This all works.

But will I select a symbol from the symbols chart it will appear in the cell that is double clicked. but then end up as a letter instead of a symbol.

But, the gdt chart works perfect.

I thought Cancel = True Would do the trick but does not. Any ideas what is missing??

This code below is in the sheet module

Private Sub Worksheet_BeforeDoubleClicK(ByVal Target As range, Cancel As Boolean)
With Application
Cancel = True............

View 9 Replies View Related

Excel 2010 :: Pivot Table - Manual Edits Don't Revert On Refresh

Oct 22, 2012

If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.

Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.

This behavior is different from what i remember from, say, excel 97 / 2000.

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

Auto-updating Formula For Week Over Week Change?

Dec 11, 2013

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

View 3 Replies View Related

Delete And Insert Record Week On Week

Aug 6, 2008

I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.

Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................

View 9 Replies View Related

Week Number Caluclating Hours For Week

Mar 6, 2010

I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.

The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2

2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time

When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel

i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007

how do i calculate weekly hours by date and weeknum ?

Total Work per day

Total Travel per day


View 9 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.



=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

View 4 Replies View Related

Formula To Tell When Its Week 1 Or Week 2 Of A Any Given Month

Jun 16, 2014

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

View 6 Replies View Related

Comparing Files From Week To Week

Feb 5, 2009

I have to download a report every week and have been manually checking the report weekly for changes.

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

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

View 10 Replies View Related

Macro To Unhide Columns - Call A Macro Then Revert Columns To Previous State

Jul 17, 2014

I have on sheet1 a number (72 at the moment) of Form CheckBoxes.

In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)

Then, go through and Check All checkboxes

Call MyMacro

Once MyMacro is complete (Filtering & Printing)

Revert the checkboxes to their original state.

The purpose of the checkboxes:

When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden

Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.

View 11 Replies View Related

Convert Date To Year/week Of Year/day Of Week

Apr 6, 2007

Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

View 9 Replies View Related

First Day Of Week

Jun 21, 2006

How can i find the first day of a week

I have the week number and i need the date of monday of that week

Ex: Week number:24
First day of week(Monday):12-06-2006

View 5 Replies View Related

Day Of The Week

Jun 27, 2006

I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.

I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this

vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate")

vToday is dimmed as variant.

I am getting a type mismatch error and I'm not sure how to correct it.

What I am aiming for eventually, is the name of the day of the week.

View 4 Replies View Related

Filter For Day Of The Week

Feb 11, 2014

In example file i wrote different dates along the column. How i can i write a filter in Table for showing only Thursdays from those dates?

File: [URL] .....

View 7 Replies View Related

Add Totals By Week

Oct 9, 2008

Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...

Column A Column G
date $ Amount

my data example...

10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/11/2008 $8.00
10/12/2008 $25.00
10/13/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...

Week 40 $50.00
Week 41 $100.00

View 3 Replies View Related

Month Day Day Of Week

Nov 2, 2008

1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week


cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.

View 3 Replies View Related

Group By 'week'?

Apr 6, 2009

I created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?

View 3 Replies View Related

Evaluating The Day Of Week ?

Jun 5, 2009

Can somebody have a look at the workbook that I've attached.

I have set up a timeshhet calculator for work but the thing is, the hourly rates of pay are dependent upon the day of the week the Operative is working, and the time of the day.

We split the shifts AM & PM and the rates are decided as per the worksheet contained within the workbook "Rates".

The problem I am having is how to get the timesheet to understand the days.

For example, if an Op works the Sat night or Sun night, then the rate should reflect x1.5. If it's Sat/Sun daytime, then it's x1.25.

Again, and as per the "Rates" sheet (see top for the rate multiplier) Mon - Fri 09:00 - 17:00 is flat rate/standard hrs. If the work Mon - Fri evenings, then the rate is x1.25.

How can this be done?

View 10 Replies View Related

Looping Through Day Of Week

Mar 17, 2009

I'm trying to set up a simple macro to loop through some number of dates on a sheet and blank out fields next to every instance of Saturday and Sunday. The only problem I'm having is actually finding the values "Saturday" and "Sunday"

Given a date in this format: 01/01/2009
I can convert to "Thursday"

Problem is, the vba code still "sees" 01/01/2009

I've tried copy / paste special / paste values to no avail.
I've tried changing it via VBA (a few different methods, including strings, formats, etc) with no luck. Even tried referencing my date cells from another spreadsheet and pasting the values back in. Can't seem to get 01/01/2009 to be "Thursday"

View 4 Replies View Related

Set Up A 6 Day Work Week

Aug 10, 2006

I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,

View 9 Replies View Related

How To Set Monday As First Day Of Week

Jan 26, 2014

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 Related

[VBA] Day Of Week Filtering

Sep 16, 2007

I've been trying to write code to filter a list of date base on day of week (Mon, Tue, etc)

My problem is that i can't find a way to code the filter criteria... since the date does not contain any week of day constant. i don't want to create another column in my sheet to show the week of day constant and filter this instead.

Here's my not yet finished code...

Sub DayOfWeek()
Dim sAnswer As String
Dim bWkDay As Byte
Dim loMyData As Range
Dim iFilterCriteria As Integer
Dim Dateserial As Integer
Application.ScreenUpdating = False

sAnswer = InputBox("Please select day to display data (first 3 letter of the day, eg. mon)", Title:="Select day to display")

View 9 Replies View Related

Average Day Of The Week

Jan 15, 2008

I have a spreadsheet where column a = date, b = weekday and c= total calls. I have this array formula to get the true average for the entire month. {=average(if(isnumber(c4:c34),c4:c34))}

The question is can I do something similar to get a true average adding in the day of the week?

What I need is the average amount of calls per weekday.

View 9 Replies View Related

Match The Week

Feb 28, 2008

I have had an attempt at an array formula, which hasn't worked out :/


Its because I want 2 different conditions as you can see above, matching both a week, and a season before summing the values. I can get it to work on a week, but then it will add up every matching week in all different seasons, and i can get it to do season, with no week but not both together :f

View 9 Replies View Related

Copyrights 2005-15, All rights reserved