Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dates and times again
Being a simple man i need a simple spreadsheet to add hours to predict a finish time and date. I want to be able to add 36 hours to 15:00 Monday and the spreadsheet tell me the finish is 03:00 Wednesday. I am not bothered about the date so don't want the hassle of having to type them in everytime. Seems simple but I can find anything suitable, any one got any suggestions?
Thanks in anticipation Martin |
#2
|
|||
|
|||
Just add them, for instance if you put 04/25/05 15:00 in A1 and 36:00 in B1,
then just add =A1+B1 and it will return 05/27/05 03:00 If you have a date with time and you use an integer for the hours you want to add 36 then you need =A1+(B1/24) you probably need to reformat result -- Regards, Peo Sjoblom "Martin B" wrote in message ... Being a simple man i need a simple spreadsheet to add hours to predict a finish time and date. I want to be able to add 36 hours to 15:00 Monday and the spreadsheet tell me the finish is 03:00 Wednesday. I am not bothered about the date so don't want the hassle of having to type them in everytime. Seems simple but I can find anything suitable, any one got any suggestions? Thanks in anticipation Martin |
#3
|
|||
|
|||
What I want is to avoid having to enter the date, I just want to add a number of hours to the start day and time and find the finish day and time Martin |
#4
|
|||
|
|||
Can't be done in that way.
-- Vasant "Martin B" wrote in message ... What I want is to avoid having to enter the date, I just want to add a number of hours to the start day and time and find the finish day and time Martin |
#5
|
|||
|
|||
"Martin B" wrote in message ... Being a simple man i need a simple spreadsheet to add hours to predict a finish time and date. I want to be able to add 36 hours to 15:00 Monday and the spreadsheet tell me the finish is 03:00 Wednesday. I am not bothered about the date so don't want the hassle of having to type them in everytime. Seems simple but I can find anything suitable, any one got any suggestions? Thanks in anticipation Martin In Cell A-! Type the start date ( Monday 15:00 ) and format as Custom dddd h:mm In Cell C1 =A1+(B1)/24 and format as Custom dddd h:mm Enter hours for prodject in B1 I got it to work the way you want I think Joe |
#6
|
|||
|
|||
How can Excel know the start date if you don't input it?
-- HTH RP (remove nothere from the email address if mailing direct) "Martin B" wrote in message ... What I want is to avoid having to enter the date, I just want to add a number of hours to the start day and time and find the finish day and time Martin |
#7
|
|||
|
|||
On Sun, 24 Apr 2005 19:27:07 +0000 (UTC), "Martin B"
wrote: What I want is to avoid having to enter the date, I just want to add a number of hours to the start day and time and find the finish day and time What rule do you want to use for Excel to compute the starting date? --ron |
#8
|
|||
|
|||
"Ron Rosenfeld" wrote in message ... On Sun, 24 Apr 2005 19:27:07 +0000 (UTC), "Martin B" wrote: What I want is to avoid having to enter the date, I just want to add a number of hours to the start day and time and find the finish day and time What rule do you want to use for Excel to compute the starting date? --ron he could always use NOW() for the start |
#9
|
|||
|
|||
On Sun, 24 Apr 2005 20:30:41 -0400, "Joe" wrote:
he could always use NOW() for the start He could use any number of rules, but he has to define one. Although I would base it on TODAY() since he's willing to enter the time (I think). --ron |
#10
|
|||
|
|||
Sorry perhaps I didn't make it clear in my original posting. This is something I have to do everyday and I always type in the start day and time, eg Monday 15:00 and a duration. I would dearly like the spreadsheet toadd the duration to the start day and time and display the finish day and time. I have no interest in the date so don't want to have to enter it multiple times. Hope this is a little clearer Thanks Martin |
#11
|
|||
|
|||
In that case:
with the start day and time in A5 and the duration in hours and minutes in B5, use the following formula in C5: =TEXT(DATEVALUE("1/1/1900")+MATCH(LEFT(A5,FIND(" ",A5)-1),{"Monday","Tuesday","Wednesday","Thursday","Fri day","Saturday","Sun day"},0)+TIMEVALUE(RIGHT(A5,LEN(A5)-FIND(" ",A5)))+B5,"dddd h:mm") No doubt Daniel M. will come up with a much more elegant solution! <g -- Vasant "Martin B" wrote in message ... Sorry perhaps I didn't make it clear in my original posting. This is something I have to do everyday and I always type in the start day and time, eg Monday 15:00 and a duration. I would dearly like the spreadsheet toadd the duration to the start day and time and display the finish day and time. I have no interest in the date so don't want to have to enter it multiple times. Hope this is a little clearer Thanks Martin |
#12
|
|||
|
|||
On Mon, 25 Apr 2005 21:51:30 +0000 (UTC), "Martin B"
wrote: Sorry perhaps I didn't make it clear in my original posting. This is something I have to do everyday and I always type in the start day and time, eg Monday 15:00 and a duration. I would dearly like the spreadsheet toadd the duration to the start day and time and display the finish day and time. I have no interest in the date so don't want to have to enter it multiple times. Hope this is a little clearer Thanks Martin Assumptions: Using 1900 date system A1: Monday (or Day of Week) B1: 15:00 (or any time) C1: Number of hours Formula: =SUM(MATCH(A1,{"Sunday","Monday","Tuesday","Wednes day","Thursday","Friday","Saturday"},0),B1,C1/24) Format result as Format/Cells/Number Custom Type: dddd hh:mm If this might be on machines using either the 1900 or 1904 date systems, then use this formula instead: =SUM(DATE(2000,1,1)+MATCH(A1,{"Sunday","Monday","T uesday","Wednesday","Thursday","Friday","Saturday" },0),B1,C1/24) --ron |
#13
|
|||
|
|||
Thanks for your help, I couldn't get your solution to work Vasant but Rons
works a treat! I can now create a list of jobs and predict the start and finish times easily. I will be grateful every time I use it Thanks |
#14
|
|||
|
|||
On Tue, 26 Apr 2005 19:23:45 +0000 (UTC), "Martin B"
wrote: Thanks for your help, I couldn't get your solution to work Vasant but Rons works a treat! I can now create a list of jobs and predict the start and finish times easily. I will be grateful every time I use it Thanks You're welcome. Thanks for the feedback. Note that if you misspell the day of the week, you will get an error message #N/A --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
merge dates and times into one column? | Excel Discussion (Misc queries) | |||
Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) | |||
Times and Dates | Excel Discussion (Misc queries) | |||
SUMPRODUCT ON TIMES | Excel Worksheet Functions |