+ Reply to Thread
Results 1 to 5 of 5

Using IF functions relating to the date of the month

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    12

    Question Using IF functions relating to the date of the month

    Hi all,

    Here is a simplified description of the data i'm dealing with:

    Left hand Column (column 1): Daily dates

    Right hand Column (column 3): Data manually entered on the first day of each month

    Middle Column (column 2): Data in column 3 divided by 30

    Issue:

    Instead of having to drag down my values manually up until the first of the next month, i want to devise a formula for column 2 that uses the data of the previous first day of the month untill the next month arrives. The purpose of this is so that i can merely alter just 1 value in the cell of colum 3 to get all the corresponding changes in the cells of column 2.

    i think this involves some sort of IF function, but am not sure exactly how to relate this to the date of the month.

    I have attatched a sample file to clarify.

    SAMPLE FILE EXCEL FORUM.xlsx

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Using IF functions relating to the date of the month

    B2=IF(C2="",B1,C2/30)
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using IF functions relating to the date of the month

    Hi Siva

    Excel has deemed the formula invalid as the logical test is incomplete (i.e. the C2="" part)
    What should i set this equal to?

    Bear in mind that i need to apply this to another file with very different cells so i hope the formula transferabble in the general case.

    Thanks a lot

    David K.

  4. #4
    Registered User
    Join Date
    08-21-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using IF functions relating to the date of the month

    NeverMind, It works when i set the logical test equal to 0. T


    Thanks

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Using IF functions relating to the date of the month

    =IF(DAY(A2)=1,C2/30,B1)
    Please Login or Register  to view this content.
    Try this

+ 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. [SOLVED] Date functions YEAR, MONTH, EOMONTH do not work for the array format?
    By alice2011 in forum Excel General
    Replies: 2
    Last Post: 05-06-2015, 08:59 PM
  2. IF/And Nestled function relating to DATE
    By rojashan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2015, 12:24 AM
  3. help with code relating to date and format
    By freshfruit in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2014, 01:30 PM
  4. [SOLVED] adding up figures relating to a range of cells with a month in
    By littlefoot in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-17-2012, 07:35 AM
  5. Relating date between sheets in same workbook
    By honestsoul in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-10-2009, 12:31 PM
  6. Replies: 5
    Last Post: 01-19-2009, 12:23 PM
  7. Relating a range of cells to the date
    By justchris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-27-2005, 12:08 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1