Formula based on dates

anum

New Member
Joined
Aug 14, 2012
Messages
44
Office Version
  1. 365
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:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Oops, that was stupid of me:

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

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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top