+ Reply to Thread
Results 1 to 7 of 7

Converting all date formats to one single standard format.

  1. #1
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Converting all date formats to one single standard format.

    Good Morning,

    I have this report which contains dates in two different formats. I need a function to make sure that the date in any of these formats be converted to one standard format given by MM/DD/YYYY.

    I have provided a spreadsheet with sample data of the format in which my date is provided. Kindly help me out on how this is done.

    Thanks,
    Booo
    Attached Files Attached Files
    Last edited by booo; 10-25-2010 at 01:30 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,788

    Re: Converting all date formats to one single standard format.

    In cell C2, put:

    Please Login or Register  to view this content.

    Format as you wish, mm/dd/yyyy, whatever, and then autofill down.

    Regards

  3. #3
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Converting all date formats to one single standard format.

    I don't quite understand. Do you want me to type just "--B2" in C2?. And where do I provide the format?

    Kindly state clearly cos I am a complete noob to excel.

    Thanks

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Converting all date formats to one single standard format.

    Hi booo,

    It looks like some of your dates are Text and some are numbers (that excel likes better)
    In C2 type = Type(B2) and pull down. It shows some are type 2 = text and some type 1 = numbers.

    The function of DateValue will convert the Text to dates. Here is my method.
    In C2 put this formula and pull down.
    Please Login or Register  to view this content.
    A fancier way to do this is to test if the cell is text and only do the datevalue then. Like:
    Please Login or Register  to view this content.
    hope that helps.

  5. #5
    Registered User
    Join Date
    09-29-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Converting all date formats to one single standard format.

    Thanks Marvin. That solved my problem.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,788

    Re: Converting all date formats to one single standard format.

    For clarification, although MarvinP has addressed your problem ...

    I meant, type "=--B2" in cell C2 without the quotes. That is, an equals sign, two minus signs and the cell reference B2. The equals sign is because it is a formula and the two minus signs force the value to be numeric.

    Or, if there aren't too many dates, you could just press F2 and enter in each date cell.

    Both options worked with ypur sample data.

    Regards

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Converting all date formats to one single standard format.

    Hi TM,

    I think the problem was his silly data was DD/MM/YYYY HH:MM AM/PM. I think the Day being first wouldn't allow =--B2 to work on my version of Excel (2010). It may have worked if I had a different date formatting as my default. That is why I went to DateValue and stripped out the parts. It will of course fail if he didn't have 2 day numbers or 2 month numbers in the string.

    I wish there was a single standard for darn dates... But...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06894 seconds
  • Memory Usage 9,020KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1