Re-set a date to the first of the month

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Vlookup Keep getting a n/a# Trying to pull second and third column pff a different worksheet Says my lookup value is the problem -- Mike Fitz Tag: Re-set a date to the first of the month Tag: 150937
  - 2
    - Ratios Hi, I am looking how to show a ratio. For instance, if I have 100 apples and 20 of them are rotten then the ratio is 5:1, i.e. 5 good apples to every bad apple. If I have 100 and 19 are bad the ratio is 5.26:1 or something like that? I'm sure there would be a formula to get the ratio to show in a cell automatically but I cannot find it. I need one decimal point. It is a while since I have used excel to make formulas etc. Thanks for any help. Mel Tag: Re-set a date to the first of the month Tag: 150933
  - 3
    - Template keeps requiring resizing/fitting after data imported I have a template to re-import text files and the column headings and column sizes keeps adjusting for some of the columns. Is there any way to keep the settings from the template the same once the data is imported ? Any ideas or suggestions ? I'm using Excel 2003 TIA George Tag: Re-set a date to the first of the month Tag: 150932
  - 4
    - Protecting data lists but allowing data entry I've recently been tinkering with Excel data lists in 2003. I can't figure out a way to protect the worksheet and still have people be able to insert new rows. The Insert button on the Row menu still works, but it generates a "Protected Cells" warning several times. The table's Insert>Row option is grayed out. I like the functionality of inserting new rows, complete with formulas, but it's rather pointless if I can't protect the formulas. Any tips on this? Thanks is advance, Doug Tag: Re-set a date to the first of the month Tag: 150930
  - 5
    - Missing something with datevalue If I type (in B2) 10/10/08 and enter =DATEVALUE(B2) (in D2) I get #VALUE! If I type (in E2) =DATEVALUE("10/10/08") I get 39731, why do I receive the #VALUE! error? Sandy Tag: Re-set a date to the first of the month Tag: 150922
  - 6
    - Macros Run In "This Workbook" vs "All Open Workbooks" Dear Fellow Excelites, I want to set a particular workbook to run macros in "This Workbook" only. I've been trying to save it with that setting in Macro -> 'Run In:' but it doesn't seem to stick. I also want to be able to copy this workbook and have the new workbook also run macros only in "This Workbook". It keeps popping back to "All Open Workbooks" at some point. :( Can you help me? Thank you, Janet Tag: Re-set a date to the first of the month Tag: 150921
  - 7
    - VBA editor & help Is there any way to keep the VBA window from re-sizing when help is used? I can start help, resize the editor, ALT-TAB to help and the editor shrinks again. Excel 2K. Thanks, Mike Tag: Re-set a date to the first of the month Tag: 150918
  - 8
    - Input form in excel Is there a way to create a permanent input form in excel to enter data and have the data recap to a master data sheet. I need to create an input form so that the users can input in fields without any issues of which line they are inputting on. What I need this one to do is if it is a Sale it populates questions that need to be answered if it is a referral a different set of questions will appear. The users do not like access so that is why I need to create this in excel. thanks Tag: Re-set a date to the first of the month Tag: 150910
  - 9
    - how to remove password pretected excel file Hi, We use Excel 2003 on Windows XP. We have an user left company with a excel file password protected. My boss want to open it. How can I help my boss open this file? Any help will be appreciated! thanks in advance! Lisa Tag: Re-set a date to the first of the month Tag: 150903
  - 10
    - EXcel 2003 / # Characters Hello, I paste text data from time to time into an excel spread sheet, and find that sometimes the result in the excel text field is filled with hash characters. The source of the data is the same in each case, yet some fields contain all # and the rest contain the correct text data. I have looked at the original to see & can't see what would cause this. Any help on this would be most appreciated. Abay Tag: Re-set a date to the first of the month Tag: 150892
  - 11
    - Can I make a name local when I create it? When I create a name by selecting the cell and typing the name in the Name Box, the name is always global. Is there any way that I can force it to be local at the time I create it? I would love to change the default to be local, if that's possible. I believe modern programming standards call for local names unless there is a very good reason to make them global. Tag: Re-set a date to the first of the month Tag: 150891
  - 12
    - Excel formula help I've come up against a problem in Excel, I need help to figure out a formula. A B C D 1 Number Of Months 24 £60.00 £1,440.00 2 Number Of Weeks 96 £15.00 £1,440.00 I will try to make this clear to understand, what I need is when I change cell C2 either adding more money or taking away, I need cell B2 to reflect the change either increasing the amount of weeks needed to get to D2s total or decrease depending on what was changed in C2. Any advice or help would be much appreciated. Tag: Re-set a date to the first of the month Tag: 150881
  - 13
    - Is it possible to include an image in page footer??? I'm trying to see if it is possible to include an image in a page footer in Excel. Haven't figured it out on my own, so I thought I'd post the question here. Thanks, Shane Tag: Re-set a date to the first of the month Tag: 150877
  - 14
    - Sharing option greyed out I have several similar workbooks that are shared on a network. Sometimes in order to do certain things (copy and paste, merge cells etc.) I have to "unshare" a book. One workbook won't let me unshare it. The checkbox beside "Allow changes by more than one user..." is checked, but greyed out. I don't know why this might be, and can't seem to figure out how to uncheck the shared option. Tag: Re-set a date to the first of the month Tag: 150862
  - 15
    - Another macro Question Hi once more: I have a workbook that includes a macro - created with some considerable help from Otto Moerback, one of the regular contributors to this group. The macro sequentially copies a named range from a group of worksheets and pastes the VALUES of the copied data into the workbook that contains the macro. This code works perfectly. The current code is as follows: Option Explicit Dim wbThis As Workbook Dim wbOther As Workbook Dim PathsList As Range Dim i As Range Dim ThePath As String Dim TheFile As String Sub MakeDatabase() With Sheets("FOLDERS") Set PathsList = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) End With Set wbThis = ThisWorkbook For Each i In PathsList ThePath = i.Value ChDir ThePath TheFile = Dir("*.xls") Do While TheFile <> "" Application.EnableEvents = False Set wbOther = Workbooks.Open(ThePath & "\" & TheFile) Sheets("DATABASE").Select Application.EnableEvents = True With wbThis.Sheets("DATA") Range("DISTFEED").Copy .Range("A6").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues End With wbOther.Close SaveChanges:=False TheFile = Dir Loop Next i End Sub Now I need to copy the actual data rather than the values of the data. I have tried simply changing the line of code where the pasting is done to the following: .Range("A6").End(xlDown).Offset(1, 0).Paste I now get the following error message on this modified line of code: Run-time error '438' Object doesn't support this property or method Any advice would be most appreciated. TIA Crownman Tag: Re-set a date to the first of the month Tag: 150861
  - 16
    - Ascii Page Break Is there any way to insert a page break into an ASCII .TXT file that Excel will recognize as such when importing? Thanks Tag: Re-set a date to the first of the month Tag: 150858
  - 17
    - Excel 2007 - Data May Be Lost - MRound function replaced with #N/A? I am one of the first people in my organisation to have Office 2007 installed - the majority of other users are still on Office 2003. As a result, I have to keep all my workbooks in Office 2003 format. The problem I have is that the message 'File error: Data my be lost' is frequently displayed when I open one of these workbooks and then several formulae are replaced with '#N/A'. I then have to go through and re-enter all the formulae. It's worth noting that this doesn't happen every time I open a workbook but after it's happened to a particular workbook once it will happen every time I open that file, even if I don't save it. It would seem that it's got something to do with the functions MROUND and NETWORKDAYS, which used to be included in the Excel 2003 Analysis TookPak. Does anyone know of any add-in, patch, etc that I can installed to get round this as I use these functions all the time and it's getting very tedious! Thanks Steve Tag: Re-set a date to the first of the month Tag: 150855
  - 18
    - Summing individual digits in a cell Hi there, I want a formula to add the digits of a cell e.g. 1234, the total would be 10, tried this forumla 'SUM(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))' and resulted in answer '1'. Please, help, i am in urgent need. Thanks Rashi Tag: Re-set a date to the first of the month Tag: 150849
  - 19
    - Complex task...can Excel do it? I have an organizational task I'm trying to accomplish. Here is the situation: At a high school summer camp I have a few hundred students. These students are of multiple grade levels, from multiple schools, and are a mix of male and female. For the summer camp we split them into equal sized groups (or as close to equal as possible) of around 12-15 students. When we create the groups, we try to: * Keep the male/female ratios within the groups the same as the general population of the camp (if out of all the students 75% are female, we would want all the groups to be 75% female as well, or as close to it as possible). * Students from the same school should be not be put together in a group (if for some reason one school brings a ton of students, each group should have one student from that school before we start doubling them up) * Each group should reflect the general population in the grade level of the students. If the general population had 10% - 9th grade, 20% - 10th grade, 50% - 11th grade, and 20% - 12th grade, the groups would all reflect the same percentages in their students. We've been doing this task manually for as long as I can remember, which takes forever...but I know it can be computerized. Can anyone think of a way to get Excel to do this for me automatically? I appreciate your help! -Ryan Tag: Re-set a date to the first of the month Tag: 150845
  - 20
    - Pluralize Is there a function to pluralize text in a cell? For example =PLURALIZE(e21) or something like that? Tag: Re-set a date to the first of the month Tag: 150844
  - 21
    - Change default filename Hi, Is there any way to change the default filename in excel 2003? ie. a blank file is opened and called: "Book X.xls" Whereas, I want all files to be called: Finance_Planning_X.xls" This way, I can save time by automatically having my file naming convention built into Excel, and I won't have to remember it going forward. Thanks! Chris Tag: Re-set a date to the first of the month Tag: 150843
  - 22
    - Changing Font Colour of row depending on selection made in 1st col I am currently trying to work out how to change the font colour of all cells in the row, depending on the selection made from a list in the 1st column (Col. A). Column A is based on selecting from a List, with about half of the selections already conditionally formatted to a certain colour. My question is - how can I get the other cells in the row to change to the same colour as the selected cell? Given that the list contains 17 different cells to select from, and 10 of these when selected display as a different colour. Currently I am manually selecting the row and changing the font colour, but would like to have it done automatically. Any help will be most welcomed. Cheers. Tag: Re-set a date to the first of the month Tag: 150834
  - 23
    - linking spreadsheets to word hi, i've linked 1 spreadsheet to another spreadsheet in excel, but now i need to link the spreadsheet to MS Word. therefore whenever i update the spreadsheet in Excel, the MS Word one will also get updated how would i do that? thankyou very much Tag: Re-set a date to the first of the month Tag: 150831
  - 24
    - Excel automatically changing my formula Hello, This is a very annoying issue I have run in to and I don't know how to work around it. I have designed a sheet with 5 rows of blank cells followed by 2 rows which sum up the previous 5. So the formulas work like this: Cell | Formula A1 | (empty) A2 | (empty) A3 | (empty) A4 | (empty) A5 | (empty) A6 | "=sum(A1:A4)" A7 | "=sum(A1:A5)" The idea is that the 6th row sums the first 4 and the 7th row sums the first 5 rows. Users enter data in the blank cells and the last two rows give them two different summaries. The problem is that Excel wants to "fix" my formula in cell A6. If a user enters a number in cell A1, then A2, etc.. through A5, then when they enter that last data point, Excel changes my formula in A6 so that it sums the first 5 rows instead of the first 4. Note that if they enter data in cell A5 first, this problem does not occur. This acts like it is some kind of annoying (dare I say, dangerous?) error checking feature and I desperately want it to stop. I am using Office Professionsal '03 with SP3 installed. However the person who brought this to me was using the sheet I created (on my computer) on his computer, which has a newer version (Vista?) of Office installed and this problem manifests when you enter data on either computer. Anyone have any ideas? Tag: Re-set a date to the first of the month Tag: 150824
  - 25
    - Marked cells more visible When marking cells in 2007, is it possible to change the color to a more visible one? Tag: Re-set a date to the first of the month Tag: 150823
- Next
  - 1
    - type date for next year I'm entering dates for 2009 in my workbook and like to enter them quick.When I type 10/29 then by default the date is returned as 10/29/2008. How can I enter a date like 10/29 in a cell so that this cell automatically sets it to 10/29/2009 and not 10/29/2008? Thanks. Tag: Re-set a date to the first of the month Tag: 150817
  - 2
    - Macro Button I used to have a button on a toolbar that I clicked and made a new button in an excel worksheet that I could assign a macro to. It's not on my toolbar anymore, and I have no idea what toolbar menu to look in to find it. Anybody know? Tag: Re-set a date to the first of the month Tag: 150809
  - 3
    - copy new worksheet I want to take a worksheet called "data" and make a copy to a closed workwork called "all data". I tried Worksheets("data").Copy after:=Workbooks("T:\me\the Data\all Data").Worksheets(Sheet1) It doesn't work...Do I have to first open the closed workbook inorder to copy this worksheet over? Thanks Tag: Re-set a date to the first of the month Tag: 150806
  - 4
    - Combining Formulas I have two columns that get data from two different formulas. The reason I have two columns is becasue I couldn't figure out how to combine the two formulas. I worked on it for a while, but I had to give up and divide it in half. I'll post the two formulas. Maybe someone can figure out an easy way to combine them. If you need more info, let me know. Thanks. =IF(C3="","",IF(B2="",IF(C3<=0,IF(D3<C3,B4,IF(B5="",IF(C3<=0,IF(D3<C3,B3,IF(B2="",IF(C3<=0,IF(D3>C3,B3,IF(B5="",IF(C3<=0,IF(D3>C3,B3))))))))))))) =IF(C3="","",IF(C3>0,"",IF(B4="",IF(C3<=0,IF(D3<C3,B2,IF(B4="",IF(C3<=0,IF(D3<C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3)))))))))))))) Tag: Re-set a date to the first of the month Tag: 150799
  - 5
    - For Each Next help Here is my code. Dim C As Range For Each C In Range("TotalsCells").Resize(1, 0) -----IT FAILS AT THIS LINE Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)" Next C I have a couple of questions. Is my variable declaration correct? I want to inspect each cell in the range. Is my Resize syntax correct for inspecting each cell? Is my Sum syntax correct? Thanks in advance for your help. Tag: Re-set a date to the first of the month Tag: 150796
  - 6
    - copy function in excel When working on an existing worksheet, I have run into a situation sometimes whereby the cell turns blue and I am unable to copy. I don't understand what is going on and what I need to change to allow me to copy. I am currently using Excell 2007 version. Tag: Re-set a date to the first of the month Tag: 150791
  - 7
    - Converting Miles and Chains Is there a function that would convert this: 24M 23Â½C To this: 024.0517 Basically that's miles and chains converted to miles and yards with the removal of the alpha characters and returned in the exact format as shown. Thanks Tag: Re-set a date to the first of the month Tag: 150790
  - 8
    - How to delete a checkbox on a Excel sheet ? Hello, I am using Excel 2003. I have created a template (.xlt) with some checkboxes. No macro in the template. No action related to the checkbox (only for printing). I would like to delete these checkboxes now but I cannot go back to the design mode. Whenever I click on a checkbox (left or right), it does nothing but check the box ! How can I do ? Thank you in advance, Gilbert Tag: Re-set a date to the first of the month Tag: 150783
  - 9
    - Formula Problem I seem to have trouble w/ a particular formula. I am attempting to round a number to the nearest tenth. No big deal...simple stuff if it were to stop there. I have rounded the number which returns as a decimal. I don't have trouble with any other number except when dealing with a zero in the 10ths spot. When I add text to the formula the decimal is not shown. Is there a way to have Excel first determine whether I have a zero in the tenths column and if so, have it show the decimal? Here's an example of the formula... FORMULA RESULT =ROUND(Battery!F30,0) 2.00 =ROUND(Battery!F30,0)& " Days" 2 Days I want it to show 2.0 Days Make sense? Thanks! Tag: Re-set a date to the first of the month Tag: 150776
  - 10
    - Help on setting a range Here is my code. Dim LastRow, LastCol As Long Dim LastCell As Range Dim LastCellAddress As Range Dim intNumCols As Long Dim DataCells As Range 'Find the last row, column and last cell of the worksheet. LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set LastCell = ActiveSheet.Cells(LastRow, LastCol) 'Select from "A2" to the last cell. Range("A2").Select Set DataCells = Cells("A2").Resize(LastRow, LastCol).Select I keep failing at the last line, trying to set the range variable. I get a Type Mismatch error. I have tried everything. I don't understand the mismatch error. DataCells is a range correct? Are my LastRow and LastCol arguments to the Resize method good? This should be so easy. Thanks in advance for your help. Tag: Re-set a date to the first of the month Tag: 150772
  - 11
    - Summing instances of text across worksheets Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Tag: Re-set a date to the first of the month Tag: 150764
  - 12
    - text to operator Have fields that look, for example, like "2A5". I know I can use the REPLACE function to get to "2*5" by replacing the letter with the asterisk. At this point the 2*5 is a text string. Is there a way to get it to be 2 times 5 so that it gives a result of 10? Thanx in advance! Tag: Re-set a date to the first of the month Tag: 150754
  - 13
    - Click Me I've drawn a command button - ClickMe - on a work sheet and written a little code for it. [I am used to vb code in the Access environment] NOW!! how do I make the button user clickable please? -- Jim Bunton Tag: Re-set a date to the first of the month Tag: 150744
  - 14
    - order of conditional formatting rules I have a sheet that has some cells with multiple conditional formatting rules. These rules are arranged in some order, so that it stops executing the conditional formats once a certain condition has been met. My problem is that when I make a copy of this sheet, the order of conditional formatting rules is not maintained on the copy. Why is this? Does anyone know how to fix it, short of manually (and tediously) rearranging the order of rules on the copy? Thanks in advance. Tag: Re-set a date to the first of the month Tag: 150741
  - 15
    - Nothing in cell only in formula bar Why is it (2007) that sometimes when I type a formula (or anything for that matter) into a cell nothing shows up in the cell, but it will show up in the formula bar? Tag: Re-set a date to the first of the month Tag: 150739
  - 16
    - Excel 2002 debug prompt for all macros We have 5 PCs in our department that should all be configured alike. When we run any macro (includes VBS script) in Excel 2002 on one of the PCs, there is a debug prompt. On the other 4 PCs the macros work fine. Is there a setting that could have been turned on that is causing the debug prompt on this computer? Chris Wieburg Tag: Re-set a date to the first of the month Tag: 150738
  - 17
    - Macro Question Hi: I have a group of several files with a simple macro that automatically goes to a specific worksheet at the time the file is opened. The code is as follows: Private Sub Workbook_Open() Worksheets ("TITLE").Select Range ("a1").Select End Sub The macro works properly when a single file is opened, but when I try to open several of the files at the same time I get the following error message: Run Time Error 1004 Select method of Worksheet Class Failed All of the files do open, but the macro appears to run on only the first file selected. All of the other files open to the page that was active when the file was last saved. Can my macro be modified to eliminate this behavior? TIA for any advice. Crownman Tag: Re-set a date to the first of the month Tag: 150728
  - 18
    - Linking cells containing text from one worksheet to a cell with text I have a workbook with two pages, the first page has cells that contain the main description heading of the cells on the second page. The second page has the heading in one cell and then the breakdown of each section under that, and repeated as there are about 11 sections. When I paste a link or use a hyperlink on the first page, it does take you to the heading cell on the second page, however I was wondering if there is a way to force the cells on the bottom of the page to the top for easier viewing. Right now it just pulls those cells up enough to see them at the bottom of the screen, and it's hard to tell where you should be looking. It would be so much cleaner if they automatically were pulled up to the top so the reader could start reading without having to scroll further. I know this is a bad description, but now sure how else to describe. I'm not really familiar with VB, I've muddled through it before, but am not good with it. thanks for any help. Tag: Re-set a date to the first of the month Tag: 150727
  - 19
    - Value Error I copied some data from a web page and pasted it into a spread sheet. It looks like the following: 10/12/2008 BALTIMORE RAVENS 39 39 13:00 EST INDIANAPOLIS COLTS -6 -4.5 Simple enough. when I try to subtract (or add) -6 and -4.5 into a new cell I get a value error. If I retype the -4.5 to -4.5 (I can't see why there is any difference) it works. (I don't have to retype the -6???, just the -4.5) I don't want to manually change all of the numbers so I can do simple subtraction and addition. This is frustrating. Tag: Re-set a date to the first of the month Tag: 150723
  - 20
    - Adding comma seperated values within a single cell I need to come up with a custom function that will add up comma seperated numerals contained within a single cell. For example if the the cell A1 contains: 4,5,3 I want the result of the fuction (say in a2) to display the result of 12 Any help greatly appreciated. Regards Mike Tag: Re-set a date to the first of the month Tag: 150716
  - 21
    - Apply any mathematical function Hi I have a bit of a tricky question. Three identical integers (ex. 3, 3, 3). Apply any mathematical funtion to each to make the sum=6. In the case of 3: 3*3-3=6 or -3+3*3. The integers are from 1 through 9. Always in identical triplets. Obviously this is one of those annoying viral emails. I thought I might give it a shot with Excel rather than to just brute force it. Got stuck though. Any thoughts? /Sune Tag: Re-set a date to the first of the month Tag: 150714
  - 22
    - Apply any mathematical funtion Hi I have a bit of a tricky question. Three identical integers (ex. 3, 3, 3). Apply any mathematical funtion to each to make the sum=6. In the case of 3: 3*3-3=6 or -3+3*3. The integers are from 1 through 9. Always in identical triplets. Obviously this is one of those annoying viral emails. I thought I might give it a shot with Excel rather than to just brute force it. Got stuck though. Any thoughts? /Sune Tag: Re-set a date to the first of the month Tag: 150712
  - 23
    - Excel Autofilters Hi All, I have a user who is experiencing problems with Autofilters in her excel spreadsheet. The user is running windows XP with office 2002 (XP). When the user receives the spreadsheet she turns the Autofilters on but after a while of working in the spreadsheet the filters will stop working. If the user tries to click on a filter it doesn't show the drop down list. The user is able to scroll around the sheet and enter new data just cannot select the list. The user and me have been able to replicate the problems on other computers. Does anyone have any ideas what could be causing this ? Tag: Re-set a date to the first of the month Tag: 150710
  - 24
    - Addition in Excel. Help! Hi, I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. i.e. +29.901 -report displayed as --> 29.90 +29.902 -report displayed as --> 29.90 +29.903 -report displayed as --> 29.90 +29.904 -report displayed as --> 29.90 +29.905 -report displayed as --> 29.91 ------------ ------------ =149.52 =149.51 (total from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. On the example, if the customer adds these value on his calculator what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency) only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! Tag: Re-set a date to the first of the month Tag: 150708
  - 25
    - SUMIF Problem - Help please I am trying to sum a column of figures in one owksheet if it matches the name in the appropriate cell in another worksheet. I know it can do it but I cannot get the formast of the formula correct - any help greatly appreciated. I am trying to sum a group of marks for particyular students names that could appear several times in the whole column ie What I want to do is sum the marks in sheet 1 over the range F4 to F10 if name in Sheet 1 A4:A100 = the name in Sheet 2 A4. I then intend to copy this formula down so it will give me total scores for each name in the class. thanks David Tag: Re-set a date to the first of the month Tag: 150704

Hello,

I need to re-set whatever date is entered in a cell to the first of
that month. For example if someone enters 9/11/2008, I need it to be
re-set to 9/1/2008.
Is this possible, and if so, can you please tell me how to do it??

thanks in advance!

Re: Re-set a date to the first of the month by Dave

Dave
Fri Oct 10 18:40:36 CDT 2008

You could use a formula in another cell:
=date(year(a1),month(a1),1)
or
=a1-day(a1)+1

Then copy|paste special|values over the original range (a column???)

OS wrote:
>
> Hello,
>
> I need to re-set whatever date is entered in a cell to the first of
> that month. For example if someone enters 9/11/2008, I need it to be
> re-set to 9/1/2008.
> Is this possible, and if so, can you please tell me how to do it??
>
> thanks in advance!

--

Dave Peterson

Re: Re-set a date to the first of the month by OS

OS
Fri Oct 10 18:55:56 CDT 2008

On Oct 10, 4:40=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You could use a formula in another cell:
> =3Ddate(year(a1),month(a1),1)
> or
> =3Da1-day(a1)+1
>
> Then copy|paste special|values over the original range (a column???)
>
> OS wrote:
>
> > Hello,
>
> > I need to re-set whatever date is entered in a cell to the first of
> > that month. =A0For example if someone enters 9/11/2008, I need it to be
> > re-set to 9/1/2008.
> > Is this possible, and if so, can you please tell me how to do it??
>
> > thanks in advance!
>
> --
>
> Dave Peterson

Hi Dave,

Both ways worked!! Thank you!