Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default (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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: (month) 2 digits in formula

Hi Bradley,

To format the month with 2 digits in your formula, you can use the
Code:
TEXT
function. Here's how you can modify your formula:
  1. =IF(B2="","",YEAR(B2)&"-"&TEXT(MONTH(B2),"00")&"-"&($A$1+C2)&"AT")

The
Code:
TEXT
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default (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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default (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





  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default (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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default (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







  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default (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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to sum the digits Bahareh Excel Worksheet Functions 14 January 29th 09 10:06 AM
How to display month in 2 digits? Eric Excel Discussion (Misc queries) 4 May 21st 08 02:21 PM
How to customize number to 10 digits including 2 digits after deci Carina Excel Worksheet Functions 3 September 20th 07 02:50 AM
Trying to get a Boolean formula to work month-to-month mstieler Excel Discussion (Misc queries) 4 July 18th 07 10:12 PM
month & year format in two digits Morphyus C via OfficeKB.com Excel Discussion (Misc queries) 1 August 5th 05 06:22 PM


All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"