Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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." |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Start/Repeat/Stop Macro at specific time/interval | Excel Programming | |||
Code for auto filling in Excel based on number of months, & start date | Excel Programming | |||
How do I get Exel to return # of hrs. worked, start time is enter | Excel Worksheet Functions | |||
VBA that automatically generates a series of cash flows based on a start date, an end date and frequency | Excel Programming | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |