CODEWITHSUNDEEP
excelmacosdatetimestamp
user6419910
user6419910

Reputation: 155

Create a timestamp given date values columns in Excel

I have a date in the following format:

Column A: Day (number form)
Column B: Month (number form)
Column C: Year (yy number form, in my case just 15 and 16 for 2015 and 2016)
Column D: Hour (number form, 0 through 23)
Column E: Minute (number form)

How can I convert this into a timestamp (namely, a timestamp representing number of minutes)? I don't see any kind of "dateserial" type function in Excel (at least on my Mac version).

Upvotes: 0

Views: 88

Answers (2)

Matias Thayer
Matias Thayer

Reputation: 621

Something like this should work:

=DATE(CONCATENATE("20",C1),B1,A1) +  TIME(D1,E1,0)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

You can do it with this formula:

=DATE(2000+C1,B1,A1)+TIME(D1,E1,0)

This will get you a number around 42000, you will need to format it in the number format you want.

enter image description here

Then if you want to time difference between two rows you would simply use:

=F2-F1

And format the cell with a custom format of [hh]:mm:ss

enter image description here

Note the method that excel stores date/time is:

Dates are whole numbers for each day since Jan 1st 1900 so we are currently in the 42000's.

Time is a decimal based on 24 hours being 1.

So both together my current time is 42531.63956 or 6/10/2015 3:21:33 or so, when the mask is applied.

Excel uses this method so we can do math on the values. The method on how the output is displayed depends on the format of the cell in which the number resides.

Upvotes: 3

Related Questions