Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Formula based on dates

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula based on dates

    Hi there,

    I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...

    Thanks!
    Anu
    Last edited by anum; Aug 6th, 2014 at 09:47 AM.

  2. #2
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    Use:

    Code:
    =IF(AND(YEAR([cell reference of heading])>=YEAR([cell reference of start date]),MONTH([cell reference of heading])>=MONTH([cell reference of start date])),[your original formula to calculate the amount here],"")
    Is that what you're looking for?

    Chris

  3. #3
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    Hi Chris,

    Thanks for this. I don't know if I'm using your formula properly, but it is not giving me the results I wanted. I can't obviously attach it but here is the link: https://drive.google.com/file/d/0B_n...it?usp=sharing

    Thanks again for your help!!


    Anu
    Last edited by anum; Aug 6th, 2014 at 10:22 AM.

  4. #4
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    I'm on a computer at work so can't click the link.

    Maybe you a bit more about how you've used the formula?

  5. #5
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    This is the formula I've typed... =IF(AND(YEAR($E$5)>=YEAR($E$5),MONTH($E$5)>=MONTH($G$5)),$C$5/12,"") and the start date is 01/09/2014 and the sal shows on every month which is incorrect

    E5= start date, C5 = annual salary, G5 = month in the year

  6. #6
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    Code:
    =IF(AND(YEAR($G$5)>=YEAR($E$5),MONTH($G$5)>=MONTH($E$5)),$C$5/12,"")
    This work?

    Chris

  7. #7
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    It doesn't seem to... when the start date was in 30/07/2013, it's giving blanks in some months (Apr-14 to Jun-15) and it showed up in Jul - Dec but nothing for Jan-Mar

  8. #8
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    Oops, that was stupid of me:

    Code:
    =IF($G$5>=DATE(YEAR($E$5),MONTH($E$5),1),$C$5/12,"")
    Working?

    Chris

  9. #9
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    Awesome! that actually worked!! can't believe it had to be that simple... Thanks a lot for your help Chris! Have a nice day

  10. #10
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula based on dates

    NP, glad I could help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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