Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 28th 14, 07:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2014
Posts: 1
Default 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   Report Post  
Old May 29th 14, 06:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 538
Default 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   Report Post  
Old May 29th 14, 12:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Start/Repeat/Stop Macro at specific time/interval andy Excel Programming 10 August 30th 07 01:38 AM
Code for auto filling in Excel based on number of months, & start date Diane Sulton Excel Programming 1 March 1st 06 02:15 AM
How do I get Exel to return # of hrs. worked, start time is enter joyce007 Excel Worksheet Functions 1 January 4th 06 04:36 PM
VBA that automatically generates a series of cash flows based on a start date, an end date and frequency [email protected] Excel Programming 0 December 27th 05 10:59 PM
Calculating days & time left from start date/time to end date/time marie Excel Worksheet Functions 7 December 7th 05 03:36 PM


All times are GMT +1. The time now is 01:08 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017