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]

 

[Rick Rothstein]

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

Dates and times in Excel (and in VBA) are floating point numbers (Excel and VBA simply change them to what we see as dates and times for our convenience)... the whole number is a number of days offset from some "date zero" (that is the number you see if you change the format of a Date cell to General) and the decimal part is the fraction of a 24-hour day represented by the time (for example, 6:00 AM would be 0.25). So, being numbers, the date being a whole number and the time a decimal value, you can simply add them together to get the date and time. As an example...

A1: 3/24/2013
A2: 12:28 PM
A3: =A1+A2

A3 would display the date and time... 3/24/2013 12:28 PM

 

[wassmer]

Thanks Rick! However, this will just add two cells but not refer to my custom date and then calculate a new date as an actual sum of both!
e.g. to get my time of day I needed to: =D2-48*INT(D2/48), then =360/48*E2+180, then =F2-INT(F2/360)*360, then =G2/15 (decimal time) and finally: =INT(H2)+(H2-INT(H2))*3/5 to get clock time.
Now - how do I do this if I know my data point 1 is 10/23/2012 0:03 (24 hour time)????

Dates and times in Excel (and in VBA) are floating point numbers (Excel and VBA simply change them to what we see as dates and times for our convenience)... the whole number is a number of days offset from some "date zero" (that is the number you see if you change the format of a Date cell to General) and the decimal part is the fraction of a 24-hour day represented by the time (for example, 6:00 AM would be 0.25). So, being numbers, the date being a whole number and the time a decimal value, you can simply add them together to get the date and time. As an example...

A1: 3/24/2013
A2: 12:28 PM
A3: =A1+A2

A3 would display the date and time... 3/24/2013 12:28 PM

 

[Rick Rothstein]

my data point 1 is 10/23/2012 0:03 (24 hour time)????

Just so I have this right before I begin... exactly what time is 0:03 to you... 3 minutes after midnight or something else?

 

[wassmer]

Thanks again - yes 3 minutes after midnight.

Best, Tom

 

[Rick Rothstein]

Thanks again - yes 3 minutes after midnight.

Okay, I am still a little fuzzy about your layout, but let me make a try at it. I am assuming that the date (10/23/2012) is in C2 and the time (0:03) is in D2 and that the formulas you showed me calculate a new time (in I2) from the time in D2. I believe the answer you want is this...

=C2+I2

If that is wrong, you will need to give me a little more detail as to where values are and what those formulas are doing.

 

[wassmer]

Hi again!
This is my table setup:

www.wassmer.org/UTAB_Squirrels2.xls

Origin position is number of 30 min. values from point 1: 10/23/2012 0:03

Thanks again!

Tom
[TABLE="width: 1154"]
<colgroup> </colgroup><tbody>[TR]
[TD="width: 100"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="width: 109"][/TD]
[TD="width: 109"][/TD]
[TD="width: 100"][/TD]
[TD="width: 100"][/TD]
[TD="class: xl65, width: 100"][/TD]
[TD="width: 100"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="class: xl67, width: 109"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="class: xl66, align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

You don't have any times and dates in that file. What cell are you looking at the formula for and what is the means of arriving at that value? How are any of those values converted to a date/time?

 

[Rick Rothstein]

This is my table setup:

www.wassmer.org/UTAB_Squirrels2.xls

Origin position is number of 30 min. values from point 1: 10/23/2012 0:03

Sorry, I am still confused. Some questions...

1) Where is your "point 1" date/time (I do not see that on the table anywhere)?

2) What do you mean by "origin position is number of 30 min values"? Do I need to know this... or is it factored into your calculations (for Row 2, cell E2 has a formula pointing to B2 and the rest of your calculation derive from the value in E2)?

3) Is what you want a formula to go into cells K2, K3, K4, etc.? If so, show me what you want the value to actually be in those cells so I can see what I am trying to calculate to. If not, then I am completely confused as to what you are looking for.

 

[wassmer]

Yes - I tried to copy & paste but the cells did not show up correct - I posted a link that contains my actual table.

Thanks! Tom

You don't have any times and dates in that file. What cell are you looking at the formula for and what is the means of arriving at that value? How are any of those values converted to a date/time?