Thanks:  0
Likes:  0

# Thread: Formula based on dates

1. ## 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

2. ## 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. ## 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

Anu

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

## User Tag List

#### Posting Permissions

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