Excel help please

danswan · 20 Nov 2007 at 11:26

Hi there

I think this should be fairly simple if you know how (hope so)

I am just trying to make a simple spreadsheet to keep track of the families finances. As part of this I want to be able to calculate how many days until payday, and divide my disposable income by these number of days, so we know how much we have per day.

Bearing in mind I get paid on the 25th of the month, how can I get excel to do this? So for instance, if it is the 29th of Nov, I want Excel to be able to work out that it is 26 days until payday.

Thanks in advance

wonder_lander · 20 Nov 2007 at 12:09

=IF(DAY(TODAY())>25,DATE(YEAR(TODAY()),MONTH(TODAY()+1),25)-TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()),25)-TODAY())

Something like that?

A[L]C · 20 Nov 2007 at 12:14

jesus, how did you create that

MarcLister · 20 Nov 2007 at 12:14

wonder_lander - That is precisely what I was thinking. Hadn't gotten around to writing it out but that is how I was imagining it in my head.

wonder_lander · 20 Nov 2007 at 13:06

Changed my mind, think you may need this one in case your in december as it'll try to create 13 months:

=IF(MONTH(TODAY()=12),IF(DAY(TODAY())>=25,DATE(YEAR(TODAY())+1,MONTH(TODAY())-11,25)-TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()),25)-TODAY()),IF(DAY(TODAY())>=25,DATE(YEAR(TODAY()),MONTH(TODAY())+1,25)-TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()),25)-TODAY()))

danswan · 20 Nov 2007 at 13:32

Well I have no idea how, but that appears to work!

Thanks

MarcLister · 20 Nov 2007 at 13:52

danswan said:
Well I have no idea how, but that appears to work!

Thanks

Don't ask him to explain it please! :eek:

He'll probably crash the forum trying to.

wonder_lander · 20 Nov 2007 at 14:18

MarcLister said:
Don't ask him to explain it please! He'll probably crash the forum trying to.

LOL, imagine what it did to my brain!

MarcLister · 20 Nov 2007 at 14:23

*KABOOM*

Something like that?

danswan · 20 Nov 2007 at 14:27

I must admit I expected something simpler, thanks for almost crashing your brain on my behalf

Tomsk · 20 Nov 2007 at 18:42

wonder_lander said:
Changed my mind, think you may need this one in case your in december as it'll try to create 13 months:

=IF(MONTH(TODAY()=12),IF(DAY(TODAY())>=25,DATE(YEAR(TODAY())+1,MONTH(TODAY())-11,25)-TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()),25)-TODAY()),IF(DAY(TODAY())>=25,DATE(YEAR(TODAY()),MONTH(TODAY())+1,25)-TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY()),25)-TODAY()))

I don't think you need to allow for 13 months, Excel seems to automatically take care of it.

A simpler version

=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>25),25)-TODAY()

MarcLister · 20 Nov 2007 at 19:40

Tomsk - That looks cool. One little error though. The first 25 is actually written "2 5" in your post. You need to remove the space and then it works. I just copied and pasted your formula into Excel and it didn't work. I wrote your formula out again myself and I think the space is the only problem, otherwise really cool.

Tomsk · 20 Nov 2007 at 23:10

I couldn't get rid of the space by editing my post. You can see another space has also appeared when I quoted wonder_lander's formula. I figured you woukd realise the extra space wasn't supposed to be there.

I blame a forum bug.

MarcLister · 20 Nov 2007 at 23:14

Looks fine with code tags.

Me > "forum bugs"

Code:

=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>25),25)-TODAY()