This Year

[simserob]

Hi, is there a way to program with the date function this year?

For example if I wanted to say something like "Days Past in this Year" or something (sorry for the weak example), and I wanted to link everything back to January 1st of this year, I would say
=today()-date(this year,1,1)

but how would you get it to work for this year, and so it would reset itself every January 1st?

[VBA Noob]

Sometime like

=TODAY()-DATE(YEAR(TODAY()),1,1)+1&" Days Past in this Year"

VBA Noob

[Trevor Shuttleworth]

Something like:

=TODAY()-DATE(YEAR(TODAY()),1,1)

Regards

Trevor


"simserob" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, is there a way to program with the date function this year?
>
> For example if I wanted to say something like "Days Past in this Year"
> or something (sorry for the weak example), and I wanted to link
> everything back to January 1st of this year, I would say
> =today()-date(this year,1,1)
>
> but how would you get it to work for this year, and so it would reset
> itself every January 1st?
>
>
> --
> simserob
> ------------------------------------------------------------------------
> simserob's Profile:
> http://www.excelforum.com/member.php...o&userid=36635
> View this thread: http://www.excelforum.com/showthread...hreadid=573452
>

[simserob]

thanks a lot

[simserob]

btw, if you feel like it, i'd appreciate it if you explained what the whole "DATE(YEAR(TODAY()),1,1)" is about, i don't really understand how it's working and i just think it might help me in the future if i understood the logic.

thanks again

[Trevor Shuttleworth]

The easiest way to understand it is to build it up in separate cells, step
by step, so you can see what values you get.

=TODAY() gives you today's date, so right now, 20/08/2006 (dd/mm/yyy)
=YEAR(TODAY()) gives you the year from today's date, so 2006
=DATE(YEAR(TODAY()),1,1) gives a date value using this year, month 1 and day
1, that is, 01/01/2006

If you were to format the cell as General or as Number you'd see that this
is 38718, the number of days since 01/01/1900

The numeric value for Today's date is 38949.

Hence you can use those values to calculate the number of days between two
dates, etc.

Regards

Trevor


"simserob" <[email protected]> wrote in
message news:[email protected]...
>
> btw, if you feel like it, i'd appreciate it if you explained what the
> whole "DATE(YEAR(TODAY()),1,1)" is about, i don't really understand how
> it's working and i just think it might help me in the future if i
> understood the logic.
>
> thanks again
>
>
> --
> simserob
> ------------------------------------------------------------------------
> simserob's Profile:
> http://www.excelforum.com/member.php...o&userid=36635
> View this thread: http://www.excelforum.com/showthread...hreadid=573452
>

[simserob]

i get it now
thanks a lot