Dates and times again

[Martin B]

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

 

[Peo Sjoblom]

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


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

 

[Martin B]

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

 

[Vasant Nanavati]

Can't be done in that way.

 

[Joe]

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

 

[Bob Phillips]

How can Excel know the start date if you don't input it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

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

 

[Joe]

time

What rule do you want to use for Excel to compute the starting date?


--ron

he could always use NOW() for the start

 

[Ron Rosenfeld]

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

 

[Martin B]

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

 

[Vasant Nanavati]

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","Friday","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>

 

[Ron Rosenfeld]

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","Wednesday","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","Tuesday","Wednesday","Thursday","Friday","Saturday"},0),B1,C1/24)


--ron

 

[Martin B]

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

 

[Ron Rosenfeld]

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