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

  • Thread starter Thread starter abhi garg
  • Start date Start date
A

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.
 
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
Click to expand...

in C2:
=B2+A2/24


Regards
Claus B.
 
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
 
Hi,

Am Wed, 28 May 2014 11:34:10 -0700 (PDT) schrieb abhi garg:
But each cell i have to drag the previous entry ?
Click to expand...

paste the formula in C2 and drag it down.


Regards
Claus B.
 
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.
 
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.
Click to expand...
 
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
Click to expand...
 
Back
Top