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

[abhi garg]

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.

 

[Claus Busch]

Hi,

Am Wed, 28 May 2014 11:08:32 -0700 (PDT) schrieb abhi garg:

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

in C2:
=B2+A2/24


Regards
Claus B.

 

[abhi garg]

Hello,

But each cell i have to drag the previous entry ?
Only able to see changes in C2 cell. What to do with all next cells (B and C) afterwards.

Thanks

 

[Claus Busch]

Hi,

Am Wed, 28 May 2014 11:34:10 -0700 (PDT) schrieb abhi garg:

But each cell i have to drag the previous entry ?

paste the formula in C2 and drag it down.


Regards
Claus B.

 

[abhi garg]

Hello Claus,

Thank.Only my last question on this solution.

Yes C2 cell got reflected after drag it down.
But Say as per current scenerio,

// formula
C2 fx : =B2+A2/24

// actual input in excel sheel
value start end
1 5/23/2014 12:00 PM 5/23/2014 01:00 PM
2 5/23/2014 12:00 PM 5/23/2014 02:00 PM
0.5 5/23/2014 02:00 PM 5/23/2014 02:30 PM

But, here for second, third, .. entries in C1. I have to manually copy the C2 end date into B2, B3 and so on. after that I can use ur formula to reflect the C* cell based on value. Any other formula is there to copy the end date/time from previous cell into next cell's start date/time. So that C2 formula/drag downward will work ?

Sorry actually I am new to Excel/Formula ?

Thanks again.

 

[abhi garg]

I think : B3 = C2. right ?

Hello Claus,



Thank.Only my last question on this solution.



Yes C2 cell got reflected after drag it down.

But Say as per current scenerio,



// formula

C2 fx : =B2+A2/24



// actual input in excel sheel

value start end

1 5/23/2014 12:00 PM 5/23/2014 01:00 PM

2 5/23/2014 12:00 PM 5/23/2014 02:00 PM

0.5 5/23/2014 02:00 PM 5/23/2014 02:30 PM



But, here for second, third, .. entries in C1. I have to manually copy the C2 end date into B2, B3 and so on. after that I can use ur formula to reflect the C* cell based on value. Any other formula is there to copy the enddate/time from previous cell into next cell's start date/time. So that C2 formula/drag downward will work ?



Sorry actually I am new to Excel/Formula ?



Thanks again.

 

[Claus Busch]

Hi again,

Am Wed, 28 May 2014 11:52:42 -0700 (PDT) schrieb abhi garg:

I think : B3 = C2. right ?

yes, you are right
B3: =C2 and drag down


Regards
Claus B.

 

[abhi garg]

Claus,
Thank you so much.
Cheers.

Hi again,



Am Wed, 28 May 2014 11:52:42 -0700 (PDT) schrieb abhi garg:






yes, you are right

B3: =C2 and drag down





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional