This Year

  • Thread starter Thread starter simserob
  • Start date Start date
S

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?
 
Something like:

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

Regards

Trevor
 
Something like:

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

Regards

Trevor
 
Sometime like

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

VBA Noo
 
Sometime like

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

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

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

thanks agai
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Vba to change displayed year automatically. 14
Dates from Text 4
Formula for converting xx/xx/xxxx dates to a number between 1 and 2
Listing only the workdays of a month 4
Excel PRICE function: How does it compute Issue Date of the Security 5
Allocate an income payment over a date range (tax years) 7
I need help with a Function 0
Hello happy new year to all, Text box input for year of purchase 0

Back
Top