Date formula required.

[sajjjid]

A1=today() that gives
03-07-2017

i need @ B1=
06-June-2017

instead of July, I need June
and "06" for June

Thanks.

 

[SirJB7]

Hi, sajjjid!
Try this in B1:
=FECHA(AÑO(A1);MES(A1)-1;DIA(A1)+3) -----> in English: =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+3)
Regards!

 

[sajjjid]

06 for june
07 for july
08 for august.

 

[SirJB7]

Hi, Sajjjid!
What are you trying to do? In your first post you asked for a formula that keep the year, subtract 1 from the month and add 3 to the day, as I understood.
Now you're asking for something else. Could you please elaborate?
Regards!

 

[sajjjid]

I dont want to add 3 for the day.
I want the month in figure as 06
06-june-2017
Next month it will read as 07-july-2017
I dont want days.
Month-Month-Year
Thanks.

 

[SirJB7]

Hi, sajjjid!
Ok, then in B1:
=A1
And with Ctrl-1 set custom format to: mm-mmm-yyyy
Regards!

 

[SirJB7]

Hi, sajjjid!
Another way, in B1:
=TEXTO(A1;"mm-mmm-yyyy") -----> in English: =TEXT(A1,"mm-mmm-yyyy")
The difference between both is that in the previous case cell A1 behaves like a number and in this one it does like text. While keeping identical what is displayed.
Regards!

 

[sajjjid]

Yes this formula works. =TEXT(A1,"mm-mmm-yyyy")
But instead of 07-July-2017 which is current month, i need 06-June-2017.
Maybe small modification. Thanks.

 

[sajjjid]

I joined both of your formula as
=TEXT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))),"mm-mmmm-yyyy")
This works. Thanks very Much...

 

[Khalid NGO]

Hi Sajjid,

You can also use EDATE function:
=EDATE(B1,-1)-1

-1 is for 1 month back
-1 is for 1 day back.

Format the cell as required.

Regards,

 

[SirJB7]

Hi, sajjjid!
Glad you solved it. But take care that the output you're getting will change every month since you have today's date in cell A1. That's to say, where it now displays "06-jun-2017" will display "07-jul-2017" on August 1st, "08-aug-2017" on September 1st, and so on.
Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

 

[sajjjid]

Hi, sajjjid!
Glad you solved it. But take care that the output you're getting will change every month since you have today's date in cell A1. That's to say, where it now displays "06-jun-2017" will display "07-jul-2017" on August 1st, "08-aug-2017" on September 1st, and so on.
Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

Yes I want that. It should change every month. Thanks.

 

[bosco_yip]

I joined both of your formula as
=TEXT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))),"mm-mmmm-yyyy")
This works. Thanks very Much...

1] The following formula will give wrong results :

=TEXT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))),"mm-mmmm-yyyy")

Here is the Testing :

2] In Column A, all date were end of the month,

In Column B B2, enter formula and copy down:

=DATE(YEAR(A2),MONTH(A2)-1,DAY(A2))

In Column C C2, enter formula and copy down:

=TEXT(B2,"mm-mmmm-yyyy")

Results appeared wrongly at the month of March, May, July, October and December. (as highlighted in red color at the above picture)

3] Reason of the wrong result.

Example, in the month of March 2017 :

In A4 : 31/03/2017

In B4, formula :

=DATE(YEAR(A4),MONTH(A4)-1,DAY(A4))

>>

=DATE(YEAR(0+"31/03/2017"),MONTH(0+"31/03/2017")-1,DAY(0+"31/03/2017"))

>>

=DATE(2017,3-1,31)

>>

=DATE(2017,2,31)

>>

=03/03/2017 -->> Wrong result !

4] Using Khalid's formula in post #.10 + Text function

In Column E E2, enter formula and copy down:

=TEXT(EDATE(A2,-1),"mm-mmmm-yyy")

All results appeared correct.

5] See attached file.

Regards
Bosco

 

[sajjjid]

View attachment 43107

1] The following formula will give wrong results :

=TEXT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))),"mm-mmmm-yyyy")

If in Column A, all date were end of the month, result will be shown wrongly at the month of March, May, July, October and December. (as highlighted in red color of the above picture Column B and C)

2] In Column E, to use Khalid's formula in post #.10 + Text function, the results appeared correct.

3] See attached file.

Regards
Bosco

Thanks Bosco,
I did not see that before.

 

[SirJB7]

1] The following formula will give wrong results :

=TEXT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))),"mm-mmmm-yyyy")

4] Using Khalid's formula in post #.10 + Text function

In Column E E2, enter formula and copy down:

=TEXT(EDATE(A2,-1),"mm-mmmm-yyy")

All results appeared correct.

Hi, bosco_yip!
Thanks for the catch.
Regards!