Custom number to date

[wassmer]

Hi guys,

I thought that I did this before but I cannot find my old files. I work on a time series that consists of 48 data points per day. The day starts at 0:30 and ends at 0:00. If I know the date and time my recording starts, I should be able to calculate date and time. I found my formulas to calculate time of day but I cannot find the formula for date and time in one - preferably as in 10/23/12 0:03

tia, Tom
[TABLE="width: 166"]
<colgroup></colgroup><tbody>[TR]
[TD="class: xl63, width: 166, align: right"]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[wassmer]

Sorry, I did not realize that the table was in German. I just replaced my upload. The reference date is not in my table yet as that is what I need to figure out. I have the formulas to get time of day. But somewhere there needs to be a formula to set datapoint 1 to 10/23/2012 0:03 and then I would like to get the date and time of any other datapoint, e.g. 49 should be 10/24/2012 0:03.

Thanks, Tom

 

[Rick Rothstein]

Sorry, I did not realize that the table was in German. I just replaced my upload. The reference date is not in my table yet as that is what I need to figure out. I have the formulas to get time of day. But somewhere there needs to be a formula to set datapoint 1 to 10/23/2012 0:03 and then I would like to get the date and time of any other datapoint, e.g. 49 should be 10/24/2012 0:03.

I was kind of hoping you would have responded to my 3 questions by now (Message #9... if you are not reading message before the last posted message, you are missing seeing responses). As to the first question, Tom seems to have asked it also. Your response of "But somewhere there needs to be a formula to set datapoint 1 to 10/23/2012 0:03" is somewhat troubling... you do realize we know nothing about what you are doing or why... you cannot expect us to set this for you as we don't know where it is supposed to come from... you need to create a place for it. I would still like to see your answers to my other questions.

 

[wassmer]

I was kind of hoping you would have responded to my 3 questions by now (Message #9... if you are not reading message before the last posted message, you are missing seeing responses). As to the first question, Tom seems to have asked it also. Your response of "But somewhere there needs to be a formula to set datapoint 1 to 10/23/2012 0:03" is somewhat troubling... you do realize we know nothing about what you are doing or why... you cannot expect us to set this for you as we don't know where it is supposed to come from... you need to create a place for it. I would still like to see your answers to my other questions.

OK - Please disregard my table. I am almost certain that this is not a difficult thing. Seems that I confused you more than helped you to understand what I need to do. If I know that 1 in column A means 10/23/2012 0:03, and I know that a day has 48 values (0.5 h recordings), then 24 is 10/23/2012 12:03, and 49 will be 10/24/2012 0:03. I just need a formula to translate the number in column A into a date/time. I cannot express it differently... I am sorry that I wasted your time and I will try to figure it out on my own as I did the formula to get time of day before...

Thanks anyway! Tom

 

[Rick Rothstein]

OK - Please disregard my table. I am almost certain that this is not a difficult thing. Seems that I confused you more than helped you to understand what I need to do. If I know that 1 in column A means 10/23/2012 0:03, and I know that a day has 48 values (0.5 h recordings), then 24 is 10/23/2012 12:03, and 49 will be 10/24/2012 0:03. I just need a formula to translate the number in column A into a date/time. I cannot express it differently... I am sorry that I wasted your time and I will try to figure it out on my own as I did the formula to get time of day before...

You did not waste my time (nor anyone else's)... I was just having trouble figuring out what you wanted, but I think I now know what you want from the examples you included in your last message. If A1 contains the date/time value 10/23/2012 0:03 and B1 contain the number of half-hour recordings, the then time resulting from adding the number of half-hour recordings in B1 to the date/time in A1 would be...

=A1+B1/48

Is that what you were looking for?

 

[wassmer]

Thanks Rick. It does something right but it is 12 hours ahead...
Thanks, Tom

You did not waste my time (nor anyone else's)... I was just having trouble figuring out what you wanted, but I think I now know what you want from the examples you included in your last message. If A1 contains the date/time value 10/23/2012 0:03 and B1 contain the number of half-hour recordings, the then time resulting from adding the number of half-hour recordings in B1 to the date/time in A1 would be...

=A1+B1/48

Is that what you were looking for?

 

[Rick Rothstein]

Thanks Rick. It does something right but it is 12 hours ahead...

I used these examples that you posted to develop the formula...

"If I know that 1 in column A means 10/23/2012 0:03, and I know
that a day has 48 values (0.5 h recordings), then 24 is
10/23/2012 12:03, and 49 will be 10/24/2012 0:03."

If A1 contains 10/23/2012 0:03 and B1 contains 24, then the formula returns 10/23/2012 12:03 like your example shows; and when B1 contains 49, the formula returns 10/24/2012 0:03 again, like your example shows. Can you clarify your examples for us... I am particularly interested in what you want when B1 contains 0 (zero)?

 

[wassmer]

When I try it using 89 it returns 20:03 but it should be 8:03...

I used these examples that you posted to develop the formula...

"If I know that 1 in column A means 10/23/2012 0:03, and I know
that a day has 48 values (0.5 h recordings), then 24 is
10/23/2012 12:03, and 49 will be 10/24/2012 0:03."

If A1 contains 10/23/2012 0:03 and B1 contains 24, then the formula returns 10/23/2012 12:03 like your example shows; and when B1 contains 49, the formula returns 10/24/2012 0:03 again, like your example shows. Can you clarify your examples for us... I am particularly interested in what you want when B1 contains 0 (zero)?

 

[Rick Rothstein]

When I try it using 89 it returns 20:03 but it should be 8:03...

How do you get 8:03? 89 half hours is 44.5 hours... 24 hours of that takes you from 10/23/2012 0:03 to 10/24/2012 0:03 and the remainder, 20.5 hours, takes you to 10/24/2012 20:33 which is 10/24/2012 8:33 PM.

 

[wassmer]

Thanks again Rick,
You are perfectly right and I apologize. I was trying to do another data juggling project trying to import US Navy twilight data into another excel sheet, which also turned out to be cumbersome. The particular file I used starts at 10/21/2013 12:03 but I had to exclude the first 1.5 days of data - therefore I was off with the datapoint to time conversion.
Your amazingly simple formula works - I don't know why I involved such complicated formulae earlier to get the time of day...

Thanks a lot!!

Tom

How do you get 8:03? 89 half hours is 44.5 hours... 24 hours of that takes you from 10/23/2012 0:03 to 10/24/2012 0:03 and the remainder, 20.5 hours, takes you to 10/24/2012 20:33 which is 10/24/2012 8:33 PM.