(month) 2 digits in formula
 

Hi! there,

I appreciate if someone can help me to solve this.
I have formula in A2,3,4.. cell like
=IF(B2="","",YEAR(B2)&"-"&MONTH(B2)&"-"&($A$1+C2)&"AT").
0
2009-3-1AT 06-Mar-09 1
2009-3-2AT 07-Mar-09 2
2009-3-3AT 08-Mar-09 3

I want the result to be "2009-03-01AT" instead of "2009-3-1AT".
I have no idea how to format 2 digits in month place.

Thanks in advance

Bradley

Answer: (month) 2 digits in formula
 

Hi Bradley,

To format the month with 2 digits in your formula, you can use the function. Here's how you can modify your formula:

1. =IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&($A$1+C2)&"AT")

The function takes two arguments: the value you want to format, and the format you want to apply. In this case, we're using "00" as the format, which means to display the month with 2 digits, adding a leading zero if necessary.

(month) 2 digits in formula
 

Try it like this:

=IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&$A$1+C2&"AT")


--
Biff
Microsoft Excel MVP


"Bradley" wrote in message
...
Hi! there,

I appreciate if someone can help me to solve this.
I have formula in A2,3,4.. cell like
=IF(B2="","",YEAR(B2)&"-"&MONTH(B2)&"-"&($A$1+C2)&"AT").
0
2009-3-1AT 06-Mar-09 1
2009-3-2AT 07-Mar-09 2
2009-3-3AT 08-Mar-09 3

I want the result to be "2009-03-01AT" instead of "2009-3-1AT".
I have no idea how to format 2 digits in month place.

Thanks in advance

Bradley

(month) 2 digits in formula
 

Or, maybe this:

=IF(B2="","",TEXT(B2,"yyyy-mm")&"-"&$A$1+C2&"AT")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

=IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&$A$1+C2&"AT")


--
Biff
Microsoft Excel MVP


"Bradley" wrote in message
...
Hi! there,

I appreciate if someone can help me to solve this.
I have formula in A2,3,4.. cell like
=IF(B2="","",YEAR(B2)&"-"&MONTH(B2)&"-"&($A$1+C2)&"AT").
0
2009-3-1AT 06-Mar-09 1
2009-3-2AT 07-Mar-09 2
2009-3-3AT 08-Mar-09 3

I want the result to be "2009-03-01AT" instead of "2009-3-1AT".
I have no idea how to format 2 digits in month place.

Thanks in advance

Bradley

(month) 2 digits in formula
 

Hi! Valko,

Thanks a lot and that is what i want. :)

Bradley

"T. Valko" wrote:

Or, maybe this:

=IF(B2="","",TEXT(B2,"yyyy-mm")&"-"&$A$1+C2&"AT")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

=IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&$A$1+C2&"AT")


--
Biff
Microsoft Excel MVP


"Bradley" wrote in message
...
Hi! there,

I appreciate if someone can help me to solve this.
I have formula in A2,3,4.. cell like
=IF(B2="","",YEAR(B2)&"-"&MONTH(B2)&"-"&($A$1+C2)&"AT").
0
2009-3-1AT 06-Mar-09 1
2009-3-2AT 07-Mar-09 2
2009-3-3AT 08-Mar-09 3

I want the result to be "2009-03-01AT" instead of "2009-3-1AT".
I have no idea how to format 2 digits in month place.

Thanks in advance

Bradley

(month) 2 digits in formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bradley" wrote in message
...
Hi! Valko,

Thanks a lot and that is what i want. :)

Bradley

"T. Valko" wrote:

Or, maybe this:

=IF(B2="","",TEXT(B2,"yyyy-mm")&"-"&$A$1+C2&"AT")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

=IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&$A$1+C2&"AT")


--
Biff
Microsoft Excel MVP


"Bradley" wrote in message
...
Hi! there,

I appreciate if someone can help me to solve this.
I have formula in A2,3,4.. cell like
=IF(B2="","",YEAR(B2)&"-"&MONTH(B2)&"-"&($A$1+C2)&"AT").
0
2009-3-1AT 06-Mar-09 1
2009-3-2AT 07-Mar-09 2
2009-3-3AT 08-Mar-09 3

I want the result to be "2009-03-01AT" instead of "2009-3-1AT".
I have no idea how to format 2 digits in month place.

Thanks in advance

Bradley

(month) 2 digits in formula
 

Use following formula
=TEXT(A1,"yyyy-mm-dd")


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode