How can I calculate formula using loop?

Hello!

Excel file format as follows:

• A column: Year (from 1998 to 2018)
• B column: Day of the year
• C column: Hour

I'd like to convert the values of B and C column as year.

Doy= C/24+B

Formula is:

Time=A+(Doy/365) or Time=A+(Doy/366)

Dividing 365 or 366 is related to divide by 4. That's why I benefitted from modulo operation but I didn't do what I want.

data=xlsread('R.xlsx');
Doy=data(:,2)+(data(:,3)/24);
M = mod(data(:,1),4);
j=1;
for i=1:176784;
if M ==0
Time=data(:,1)+(Doy/366);
else M~=0
Time=data(:,1)+(Doy/365);
j=j+1;
end

Answer 1

[Walter Roberson]

data = xlsread('R.xlsx');
Doy = data(:,2)+(data(:,3)/24);
yearlen = 365 + (eomday(data(:,1),2) == 29);   %trick
Time = data(:,1) + Doy ./ yearlen;

[Suat YAZICI]

Leap year is one year in every four in which February has 29 days instead of 28 such as 2000,2004,2008, 2012 and 2016.

If year lenght equals that, doy divides by 366 for these leap years, otherwise doy divides by 365 ?

[Walter Roberson]

Very close. Centuries (years which end in 00) are not leap years unless they are also divisible by 400. So 1900 was not a leap year by 2000 was.

The eomday() routine knows about leap years, and will return 29 when asked for the last day of the month for February for any year which is a leap year. My code compares the response of eomday() to 29 to determine whether the year is a leap year; if it is not a leap year then the == returns 0, and if it is a leap year then the == returns 1. That 0 or 1 value is added to 365 to determine the length of the year, 365 for non leap years and 366 for leap years. The day of year is then divided by that amount.

[Suat YAZICI]

Thank you for helping me and explanation.