Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula date question
I am having trouble with a formula that calculates the weekending date
(Saturday) for a cell. For example, if I enter Nov 2 in cell B6, I want cell A2 to show 11/5 which is the weekending date. Ideas? Thanks, Dino |
#2
|
|||
|
|||
Formula date question
On Mon, 14 Nov 2005 12:58:18 -0500, "Dino"
wrote: I am having trouble with a formula that calculates the weekending date (Saturday) for a cell. For example, if I enter Nov 2 in cell B6, I want cell A2 to show 11/5 which is the weekending date. Ideas? Thanks, Dino =B6+7-WEEKDAY(B6) --ron |
#3
|
|||
|
|||
Formula date question
Thanks Ron. I have one more question.... this is for a project time sheet. I
have a column for Start Time and End Time, and I need to calculate the time between these two. I tried just a simple J3-J2 but it didn't work. Thanks again, Dino "Ron Rosenfeld" wrote in message ... On Mon, 14 Nov 2005 12:58:18 -0500, "Dino" wrote: I am having trouble with a formula that calculates the weekending date (Saturday) for a cell. For example, if I enter Nov 2 in cell B6, I want cell A2 to show 11/5 which is the weekending date. Ideas? Thanks, Dino =B6+7-WEEKDAY(B6) --ron |
#4
|
|||
|
|||
Formula date question
On Mon, 14 Nov 2005 13:36:17 -0500, "Dino"
wrote: Thanks Ron. I have one more question.... this is for a project time sheet. I have a column for Start Time and End Time, and I need to calculate the time between these two. I tried just a simple J3-J2 but it didn't work. Thanks again, Dino 1. What does "didn't work" mean? Excel crashed? Answer that didn't make sense to you? Excel error message? Something else? 2. What, exactly, is in J2 and J3? What do you see in the cell? What do you see in the formula bar? 3. If you didn't answer it in (1), what result did you get from the formula? --ron |
#5
|
|||
|
|||
Formula date question
Sorry for the incomplete question. I think I figured it out.
I entered a Start Time of 8:00 in J2 and in J3 entered a Stop Time of 11:15. In J4 I entered J3-J2 which gave me a value of .14 which must be in a fraction of a day. I modified my formula to (J3-J2)*24 which gave me the correct answer of 3.25. -- Thanks, Diane Thought for the day: God answers knee mail "Ron Rosenfeld" wrote in message ... On Mon, 14 Nov 2005 13:36:17 -0500, "Dino" wrote: Thanks Ron. I have one more question.... this is for a project time sheet. I have a column for Start Time and End Time, and I need to calculate the time between these two. I tried just a simple J3-J2 but it didn't work. Thanks again, Dino 1. What does "didn't work" mean? Excel crashed? Answer that didn't make sense to you? Excel error message? Something else? 2. What, exactly, is in J2 and J3? What do you see in the cell? What do you see in the formula bar? 3. If you didn't answer it in (1), what result did you get from the formula? --ron |
#6
|
|||
|
|||
Formula date question
On Mon, 14 Nov 2005 15:30:05 -0500, "Dino"
wrote: Sorry for the incomplete question. I think I figured it out. I entered a Start Time of 8:00 in J2 and in J3 entered a Stop Time of 11:15. In J4 I entered J3-J2 which gave me a value of .14 which must be in a fraction of a day. I modified my formula to (J3-J2)*24 which gave me the correct answer of 3.25. Your analysis is correct. You could have also, without multiplying by 24, formatted the cell as: [h]:mm to give a result of 3:15 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value between 2 dates | Excel Worksheet Functions | |||
Date Formula | Excel Worksheet Functions | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
Formula with date constraints | Excel Worksheet Functions | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) |