+ Reply to Thread
Results 1 to 15 of 15

Cells will become 0 automatically without changing formula

  1. #1
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Cells will become 0 automatically without changing formula

    Hi All

    I need help . Can or not if i use this formula =IF(OR(B5="",C5=""),0,DAYS360($B5,$C5,TRUE)+1) , If unpaid leave, half-pay leave and study leave, total days cells will become 0 automatically without changing formula
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    363

    Re: Cells will become 0 automatically without changing formula

    Hello,

    Not sure, what you are referring as without changing formula. See if this helps. In E5, paste the below formula and copy down

    Please Login or Register  to view this content.
    Note: Study Leave has a space in the end & half pay leaf has a typo i guess. Both needs to be corrected if needed.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Cells will become 0 automatically without changing formula

    In E5 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

  4. #4
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    how about if there is additional 1 columns. If unpaid leave, half-pay leave and study leave, total column will be 360 and total day colum will become 0
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    363

    Re: Cells will become 0 automatically without changing formula

    Just reverse the true, false value in sixth sense formula,

    =IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),DAYS360($B5,$C5,TRUE)+1,0) - in E5 and drag down

    =IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),0,DAYS360($B5,$C5,TRUE)+1) - IN F5 and drag down

  6. #6
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by elayaz View Post
    Just reverse the true, false value in sixth sense formula,

    =IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),DAYS360($B5,$C5,TRUE)+1,0) - in E5 and drag down
    Why cells B5 & C5 blank at cells E5 have 1 not 0

  7. #7
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    363

    Re: Cells will become 0 automatically without changing formula

    That's because the original formula which you don't want to change had DAYS360 +1

    Try this altered sixth sense formula in E5 and drag down


    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by elayaz View Post
    That's because the original formula which you don't want to change had DAYS360 +1

    Try this altered sixth sense formula in E5 and drag down


    Please Login or Register  to view this content.
    if i use this formula 1/1/2013 - 2/7/2013 in e5 181 day. i want 182 day because i count 1 month average 30 day. 1/1/2013 - 30/6/2013 = 180 day, 1/7/2013 - 2/7/2013 = 2 day. All day is 182.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Cells will become 0 automatically without changing formula

    Try this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by :) Sixthsense :) View Post
    Try this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    how to include word automatically in E5. Example : 182 day

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by redza View Post
    how to include word automatically in E5. Example : 182 day
    Try this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    363

    Re: Cells will become 0 automatically without changing formula

    Sixth sense formula should work for you. Else wrap another if function in the original sixth sense formula


    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    Can or not in excel fix date based on year

    A1 (YEAR) 2004
    B1 (FROM) 1/1
    C1 (TO) 2/7

    I key in only 1/1 or 2/7 and the date change 1/1/2004 or 2/7/2004 based on year at cells A1

  14. #14
    Registered User
    Join Date
    08-26-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by elayaz View Post
    sixth sense formula should work for you. Else wrap another if function in the original sixth sense formula


    Please Login or Register  to view this content.
    formula that is correct

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Cells will become 0 automatically without changing formula

    Quote Originally Posted by redza View Post
    Can or not in excel fix date based on year
    Since it is a new question please mark this thread as solved and start a new thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel cells changing the colours automatically !!
    By binoykaladi in forum Excel General
    Replies: 2
    Last Post: 06-04-2012, 08:57 AM
  2. Replies: 1
    Last Post: 09-23-2009, 05:27 AM
  3. Automatically Changing fill colour in cells
    By frazzled in forum Excel General
    Replies: 3
    Last Post: 08-29-2009, 03:28 PM
  4. Changing color of date cells automatically
    By CLSSY56 in forum Excel General
    Replies: 20
    Last Post: 04-30-2008, 01:07 PM
  5. Calculating and changing cells automatically
    By Reign in forum Excel General
    Replies: 0
    Last Post: 07-31-2006, 03:04 AM

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.08151 seconds
  • Memory Usage 9,302KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (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
  • (5)bbcode_code
  • (6)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_attachment
  • (15)postbit_legacy
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (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 (49):
  • 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
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1