+ Reply to Thread
Results 1 to 14 of 14

Dates and times again

  1. #1
    Martin B
    Guest

    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. #2
    Peo Sjoblom
    Guest

    Re: Dates and times again

    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" <mn.bagshaw@btinternet.com> wrote in message
    news:d4gqml$iop$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
    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. #3
    Martin B
    Guest

    Re: Dates and times again


    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. #4
    Vasant Nanavati
    Guest

    Re: Dates and times again

    Can't be done in that way.

    --

    Vasant

    "Martin B" <mn.bagshaw@btinternet.com> wrote in message
    news:d4grub$go4$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
    >
    > 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. #5
    Joe
    Guest

    Re: Dates and times again


    "Martin B" <mn.bagshaw@btinternet.com> wrote in message
    news:d4gqml$iop$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
    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. #6
    Bob Phillips
    Guest

    Re: Dates and times again

    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" <mn.bagshaw@btinternet.com> wrote in message
    news:d4grub$go4$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
    >
    > 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. #7
    Ron Rosenfeld
    Guest

    Re: Dates and times again

    On Sun, 24 Apr 2005 19:27:07 +0000 (UTC), "Martin B"
    <mn.bagshaw@btinternet.com> 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. #8
    Joe
    Guest

    Re: Dates and times again


    "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
    news:qf9o6199d7si00jko8tfhkfqbuuku3vci8@4ax.com...
    > On Sun, 24 Apr 2005 19:27:07 +0000 (UTC), "Martin B"
    > <mn.bagshaw@btinternet.com> 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. #9
    Ron Rosenfeld
    Guest

    Re: Dates and times again

    On Sun, 24 Apr 2005 20:30:41 -0400, "Joe" <mme@mynet.com> 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. #10
    Martin B
    Guest

    Re: Dates and times again


    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. #11
    Vasant Nanavati
    Guest

    Re: Dates and times again

    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>

    --

    Vasant


    "Martin B" <mn.bagshaw@btinternet.com> wrote in message
    news:d4jop2$h7g$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
    >
    > 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. #12
    Ron Rosenfeld
    Guest

    Re: Dates and times again

    On Mon, 25 Apr 2005 21:51:30 +0000 (UTC), "Martin B"
    <mn.bagshaw@btinternet.com> 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","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

  13. #13
    Martin B
    Guest

    Re: Dates and times again

    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. #14
    Ron Rosenfeld
    Guest

    Re: Dates and times again

    On Tue, 26 Apr 2005 19:23:45 +0000 (UTC), "Martin B"
    <mn.bagshaw@btinternet.com> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1