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

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

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

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

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!

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!!

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

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

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!

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

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