How to enter start and end date/time in excel based on some specific value.
 

Hello,

I want to enter date with no. of hours spent - start date/time and end date/time in excel based on some value.

Say, I have three cells in Excel.


value start date and time end date and time
1 05/23/2014 10:00:00 AM 05/23/2014 11:00:00 AM
2 05/23/2014 11:00:00 AM 05/23/2014 01:00:00 PM
0.5 05/23/2014 02:00:00 PM 05/23/2014 02:30:00 PM


Actually I want to enter time into 2nd and 3rd cell based on value on 1st column.
The value is like : 1 = 1 hr, 2 = 2 hr and 0.5 = 30 mins. But in the first cell these values are entered as number, not in time format.

Please suggest me anyone , any easier way to do this because manual entry is time consuming in excel.

Thanks.

How to enter start and end date/time in excel based on some specific value.
 

abhi garg wrote:

I want to enter date with no. of hours spent - start date/time and end
date/time in excel based on some value.

Say, I have three cells in Excel.


value start date and time end date and time
1 05/23/2014 10:00:00 AM 05/23/2014 11:00:00 AM
2 05/23/2014 11:00:00 AM 05/23/2014 01:00:00 PM
0.5 05/23/2014 02:00:00 PM 05/23/2014 02:30:00 PM


Actually I want to enter time into 2nd and 3rd cell based on value on
1st column. The value is like : 1 = 1 hr, 2 = 2 hr and 0.5 = 30 mins.
But in the first cell these values are entered as number, not in time
format.

Please suggest me anyone , any easier way to do this because manual
entry is time consuming in excel.

Select the first cell of the "value" column. (I'm assuming it's A2, and
A1="value".) Enter this formula:

=(C2-B2)*24

(If the start and end columns aren't B and C respectively, or the first row
of data isn't row 2, edit to fit.)

Right-click that cell and selecting "Format cells..." Choose the "General"
format and click OK. Now copy that cell down as far as you need it.

--
There's no one thing that I do in my consulting practice.
I guess I could call it, "Rent my brain and I make you money."

How to enter start and end date/time in excel based on some specific value.
 

On Wed, 28 May 2014 11:07:21 -0700 (PDT), abhi garg wrote:

Hello,

I want to enter date with no. of hours spent - start date/time and end date/time in excel based on some value.

Say, I have three cells in Excel.


value start date and time end date and time
1 05/23/2014 10:00:00 AM 05/23/2014 11:00:00 AM
2 05/23/2014 11:00:00 AM 05/23/2014 01:00:00 PM
0.5 05/23/2014 02:00:00 PM 05/23/2014 02:30:00 PM


Actually I want to enter time into 2nd and 3rd cell based on value on 1st column.
The value is like : 1 = 1 hr, 2 = 2 hr and 0.5 = 30 mins. But in the first cell these values are entered as number, not in time format.

Please suggest me anyone , any easier way to do this because manual entry is time consuming in excel.

Thanks.

A table like the following should help. Please note that this assumes that you enter manually not only the numbers in the Value column, but also the first entry in the Start Date and Time column, as I don't know how else you would derive that.

Assume Table starts in A1

$A$1: Value
$A$2: 1
$A$3: 2
$A$4: 0.5

$B$1: Start Date and Time
$B$2: 5/23/14 10:00 AM
$B$3: =C2
$B$4: =C3

$C$1: End Date and Time
$C$2: =B2+A2/24
$C$3: =B3+A3/24
$C$4: =B4+A4/24

This works because Excel stores time in days and fractions of a day. So 1 hour = 1/24 of a day.