Years

[lehy2002]

I need to calculate the year for the following data. What I want to know for the year column is if there is a date in the Visit date 2018 column bring back 2018 in the year column, if there is a date in the visit date 2019 it will bring back 2019 in the year column, if there is a date in both, it will bring back 2018/2019 in the year column and if there are no dates in either of the visit columns the year column will be blank. I hope that makes sense. Thank you.

[TABLE="width: 344"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Visit Date 2018[/TD]
[TD]Visit Date 2019[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/16/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]8/23/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]8/23/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]8/23/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/12/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/12/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/12/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/12/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6/12/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1/25/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5/24/2018[/TD]
[TD]1/9/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7/27/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

For row 2, place this in cell C2 and copy down for all rows.

=IF(AND(A2>0,B2>0),YEAR(A2)&"/"&YEAR(B2),IF(A2>0,YEAR(A2),IF(B2>0,YEAR(B2),"")))

 

[lehy2002]

for row 2, place this in cell c2 and copy down for all rows.

=if(and(a2>0,b2>0),year(a2)&"/"&year(b2),if(a2>0,year(a2),if(b2>0,year(b2),"")))

thank you!!

 

[Joe4]

You are welcome.

 

[lehy2002]

This will give back a value error if it is 12 M or 12N. how do I get rid of that and give me the correct info?

 

[Phuoc]

Or try:

=SUBSTITUTE(TRIM(TEXT(A2,"yyyy;;;")&" "&TEXT(B2,"yyyy;;;"))," ","/")

 

[Joe4]

This will give back a value error if it is 12 M or 12N. how do I get rid of that and give me the correct info?

I do not understand what you are saying.
You have dates or blanks in your two columns, right?
What is is "12 M" or "12N" then?

 

[lehy2002]

I do not understand what you are saying.
You have dates or blanks in your two columns, right?
What is is "12 M" or "12N" then?

Oops - working on two sheets. For this one, I have the calculation
TIME(HOUR(U11438),MINUTE(U11438), SECOND(U11438))

to pull the time out of a date/time formatting in a cell. However, if the time is 12M (midnight) or 12N (noon) it gives a value error instead of the time. See what I have below:

[TABLE="width: 338"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date and Time Reviewed (Date Supervisor Reviewed) [/TD]
[TD] Time Reviewed [/TD]
[TD] AM/PM Reviewed[/TD]
[/TR]
[TR]
[TD]12/28/2017 0:26[/TD]
[TD]1/0/1900 0:26[/TD]
[TD]12:26 AM[/TD]
[/TR]
[TR]
[TD]05/23/2018
12:00 M[/TD]
[TD]#VALUE![/TD]
[TD]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]


The calculations I have are:

[TABLE="width: 934"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date and Time Reviewed (Date Supervisor Reviewed) [/TD]
[TD] Time Reviewed[/TD]
[TD] AM/PM Reviewed[/TD]
[/TR]
[TR]
[TD]12/28/17 [/TD]
[TD]=TIME(HOUR(U2),MINUTE(U2), SECOND(U2))[/TD]
[TD] =IF(V2=0,"",TIME(HOUR(U2),MINUTE(U2),SECOND(U2)))[/TD]
[/TR]
[TR]
[TD]05/23/2018 12:00 M[/TD]
[TD]=TIME(HOUR(U11438),MINUTE(U11438), SECOND(U11438)) [/TD]
[TD] =IF(V11438=0,"",TIME(HOUR(U11438),MINUTE(U11438),SECOND(U11438)))[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense

 

[Joe4]

To pull just the time out of any Date/Time value, you can simply use:

[COLOR=#333333]=U11438-TRUNC([/COLOR][COLOR=#333333]U11438)
[/COLOR]

as Date/Times are actually stored in Excel as numbers, specifically the number of days since 1/0/1900, with a custom format on it (can see this easily by changing any valid DateTime entry to General format).
So Dates are the whole number, and Time is just the fractional piece. So you just have to get the fraction if you want the time only.

But I don't see what any of this has to do with your original question?

I need to calculate the year for the following data. What I want to know for the year column is if there is a date in the Visit date 2018 column bring back 2018 in the year column, if there is a date in the visit date 2019 it will bring back 2019 in the year column, if there is a date in both, it will bring back 2018/2019 in the year column and if there are no dates in either of the visit columns the year column will be blank. I hope that makes sense. Thank you.

Did you morph into a totally separate question?