Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
Hi there!
I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
If you had actually inserted Nov-08 as an Excel date, =H1-1 would have shown
Oct-08, because Nov-08 as a date defaults to 1-Nov-08 and subtracting 1 would give 31-Oct-08. My guess is that you've got a text string in H1. Check with the formulae =ISTEXT(H1) and ISNUMBER(H1), and by trying to reformat H1 temporarily as a different date format such as dd/mmm/yyyy; if the display in H1 doesn't change, you've got text, not a date. You confused me a little by referring to the "date format and date function". If you used the DATE function, could you show us what foirmula you used? -- David Biddulph "alf" wrote in message ... Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
alf;198412 Wrote: Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf In G1 enter =DATE(YEAR(h1),MONTH(h1)-1,DAY(h1)) Substracting 1 from a date substracts one day . For more information on how XL treats dates have a look 'here' (http://www.cpearson.com/excel/datetime.htm#AddingDates) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54625 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
You can use following formula in adjacent cell=
=DATE(YEAR(H1),MONTH(H1)-1,DAY(H1)) Chris ------ Convert your Excel spreadsheet into online calculator. http://www.spreadsheetconverter.com -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
On 26 Jan., 09:34, "David Biddulph" <groups [at] biddulph.org.uk
wrote: If you had actually inserted Nov-08 as an Excel date, =H1-1 would have shown Oct-08, because Nov-08 as a date defaults to 1-Nov-08 and subtracting 1 would give 31-Oct-08. My guess is that you've got a text string in H1. *Check with the formulae =ISTEXT(H1) and ISNUMBER(H1), and by trying to reformat H1 temporarily as a different date format such as dd/mmm/yyyy; *if the display in H1 doesn't change, you've got text, not a date. You confused me a little by referring to the "date format and date function". *If you used the DATE function, could you show us what foirmula you used? -- David Biddulph "alf" wrote in message ... Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf Hi David, Thanks for your swift reply! I'll start to inform you on what you asked: 1. Excel Date didn't show Oct 08 in G1, in spite of putting Nov-08 as an excel date in Hi, because I had inserted as a Date function the last day of November [=DATE(2008;11;30)] 2. Have used the formulae istext and isnumber and I have no texts, just dates Now I'll reformulate the problem: Excel is working in a logical way (putting in G1 the 29th of November, in F1 the 28th of November etc.) - I understand it now based on your explanation, but my objective is to have on cell G1 Oct 08, on cell F1 Sep 08 etc. I have tried this by inserting in G1 the function =H1-30. This works for most of the months (Now I can live with this, by correcting the few wrong ones), but not with all (Probably because of the months with 31 days). My new question is: is there a way to avoid these manual corrections? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
On 26 Jan., 09:37, Pecoflyer wrote:
alf;198412 Wrote: Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf In G1 enter =DATE(YEAR(h1),MONTH(h1)-1,DAY(h1)) Substracting 1 from a date substracts one day . For more information on how XL treats dates have a look 'here' (http://www.cpearson.com/excel/datetime.htm#AddingDates) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*& allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=54625 I've only seen now what you purpose [In G1 enter =DATE(YEAR(h1),MONTH (h1)-1,DAY(h1))] cool! thanks!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
If you have a date in November in H1, and you want G1 to show the last
date in October, the formula to use is =DATE(YEAR(H1),MONTH(H1),0) or =DATE(YEAR(H1);MONTH(H1);0) as you are using the semi-colon as list separator. -- David Biddulph On 29 Jan, 14:35, alf wrote: Hi David, Thanks for your swift reply! I'll start to inform you on what you asked: 1.ExcelDatedidn'tshowOct08inG1, in spite of putting Nov-08 as an excel datein Hi, because I had inserted as a Datefunction the last day of November [=DATE(2008;11;30)] 2. Have used the formulae istext and isnumber and I have no texts, just dates Now I'll reformulate the problem: Excelis working in a logical way (putting in G1the 29th of November, in F1 the 28th of November etc.) - I understand it now based on your explanation, but my objective is to have on cell G1 Oct08, on cell F1 Sep08 etc. I have tried this by inserting in G1the function =H1-30. This works for most of the months (Now I can live with this, by correcting the few wrong ones), but not with all (Probably because of the months with 31 days). My new question is: is there a way to avoid these manual corrections? Thanks On 26 Jan., 09:34, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you had actually inserted Nov-08 as an Excel date, =H1-1 would have shown Oct-08, because Nov-08 as a date defaults to 1-Nov-08 and subtracting 1 would give 31-Oct-08. My guess is that you've got a text string in H1. *Check with the formulae =ISTEXT(H1) and ISNUMBER(H1), and by trying to reformat H1 temporarily as a different date format such as dd/mmm/yyyy; *if the display in H1 doesn't change, you've got text, not a date. You confused me a little by referring to the "date format and date function". *If you used the DATE function, could you show us what formula you used? -- David Biddulph "alf" wrote in message ... Hi there! I inserted adate, for example (Nov-08) in the cell H1 of anexcel sheet. I have used thedateformat anddatefunction. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.:Oct-08. I got surprised to see that writing "=H1-1" in cellG1doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
So are you happy that if H1 contains 31-Oct-2008, the resultin G1 is
1-Oct-2008? I thought you wanted a date in the previous month? -- David Biddulph "alf" wrote in message ... I've only seen now what you purpose [In G1 enter =DATE(YEAR(h1),MONTH (h1)-1,DAY(h1))] cool! thanks!! On 26 Jan., 09:37, Pecoflyer wrote: alf;198412 Wrote: Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf In G1 enter =DATE(YEAR(h1),MONTH(h1)-1,DAY(h1)) Substracting 1 from a date substracts one day . For more information on how XL treats dates have a look 'here' (http://www.cpearson.com/excel/datetime.htm#AddingDates) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*& allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=54625 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
On 26 Jan., 11:19, "Chris_Bode via OfficeKB.com" <u49096@uwe wrote:
You can use following formula in adjacent cell= =DATE(YEAR(H1),MONTH(H1)-1,DAY(H1)) Chris ------ Convert your Excel spreadsheet into online calculator.http://www.spreadsheetconverter.com -- Message posted viahttp://www.officekb.com This is it! Thank you all! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
On Mon, 26 Jan 2009 00:20:35 -0800 (PST), alf
wrote: Hi there! I inserted a date, for example (Nov-08) in the cell H1 of an excel sheet. I have used the date format and date function. Now, I'd like the software to automatically fill out the adjacent cell (s) [G1] with the previous month(s), e.g.: Oct-08. I got surprised to see that writing "=H1-1" in cell G1 doesn't make it happen; it just copies "Nov-08". Can you give me a help? Thank you in advance for your support! best, alf G1: H1-DAY(H1) will -- the last day of the previous month. Then format as mmm-yy --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date
I'm glad you're happy with that, alf, but I thought that if 31-Oct-08 was in
H1 you wanted to see a date in the previous month, i.e. September. If that's not what you want, then that's fine. -- David Biddulph "alf" wrote in message ... This is it! Thank you all! On 26 Jan., 11:19, "Chris_Bode via OfficeKB.com" <u49096@uwe wrote: You can use following formula in adjacent cell= =DATE(YEAR(H1),MONTH(H1)-1,DAY(H1)) Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
Figuring Vacation Hrs. Earned using Current Date minus Hire Date | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |