Excel display the day number of the year.

Thank you Zen for your quick response

To display the day of the year, the formula is:
=TODAY()-DATE(YEAR(TODAY());1;0)
Great, it works

To display the day of the year for a date in cell A10:
=A10-DATE(YEAR(A10);1;0)
This one doesn't work....

Best regards
Hervé

The day of the week corresponding to
April 4, 1968 is
Thursday.
Alfred

Hello

If you enter your date in a cell and then apply a custom format "dddd," you will see the day: Thursday
--

Always zen

Hello,
do I need to format the cell containing the date in full (11/09/08) in a special format (date format, number, standard)
If I set it to date format, it gives me 11/09/1900...
If I set it to number format, it calculates 11 / 09 / 2008 (for info, it shows 39702!!!!)
If I set it to standard, it does nothing

Does anyone have a solution?
Thanks in advance.

Hello

Just so you know, it gives me 39702 !!!!

That's completely normal, because it's the number of days since 01/01/1900.

The integer part of a date is indeed that number of days, and the decimal part is the hours in 24ths.

This being the value of the cell, you can display it as you wish by choosing the date format you want or even create your own format with "customize", for example "yyyy", and you'll see the day: Thursday.
--

Always zen.

Hello

You didn't quite understand the basic formula for this topic:

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

If you adapt it with the result of your calculation 177, it gives

=DATE(YEAR(TODAY()),1,0)+177 

and with a format: d mmmm you get June 26.
--

Always zen

Hi,
I am also looking for uncommon formulas, here’s one that I quite like which returns the day of the month in JS
(D18-DATE(YEAR(D18),1,)+WEEKDAY(("1/&"YEAR(D18))-MOD(D18-1,7),3))/7&". "&TEXT(D18,"dddd")
sorry for the English formula, but my XL version is UK,
it is obvious that D18 is a date :-)
regards

Hello

it returns the JS day of the month

Actually, it's the week number and the day in plain language, and here it is in the language of Molière, or almost...

=(D18-DATE(YEAR(D18);1;)+WEEKDAY(("1/"&YEAR(D18))-MOD(D18-1;7);3))/7&" . "&TEXT(D18;"jjjj") and this gives the same result =INT(MOD(INT((D18-2)/7)+0,6;52+5/28))+1&" . "&TEXT(D18;"jjjj")

--

Always zen

I am working on the break-even point. In the end, I find the number of the day of the year when the company reaches this point. But I can't find a formula to translate this day number into a date.
For example, the 177th day of the year. So June 26th.

Does anyone know the formula?
Thank you.

the simplest way is to create a simple table
one column DD/MM/YYYY
one column with a series incremented by 1
in which
row 1, col a, 01/01/2010, col b, 1
row 365, col a 31/12/2010, col b, 365

Friday, January 1, 2010 1
Saturday, January 2, 2010 2
Sunday, January 3, 2010 3
Monday, January 4, 2010 4
Tuesday, January 5, 2010 5
Wednesday, January 6, 2010 6
Thursday, January 7, 2010 7
Friday, January 8, 2010 8
Saturday, January 9, 2010 9
Sunday, January 10, 2010 10

Hello,

The day number of the year is in A1

=40178+A1

Result cell to be formatted as date.
(40178 is the counter number as of 31/12/2009)

Have a nice day!