| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time formulate
correction for my problem...
A B C D E 1 date working hours work.hors yor work.hours for work.hours last 7 days last 30 days for last year 2 12.20.05 08:15 3 01.01.06 08:00 4 03.25.06 07:55 5 04.15.06 08:45 How can i formulate each cell on column C,D, and E? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time formulate
For the total hours in the last 7 days, try...
=SUMPRODUCT(--(SUBSTITUTE(A2:A5,".","/")+0TODAY()-7),B2:B5) ....and format the cell as follows... Format Cells Number Custom Type: [h]:mm If you change the format so that Column A contains true dates, for example... 12/20/05 4/11/06 4/25/06 4/15/06 ....you could use the following formula instead... =SUMPRODUCT(--(A2:A5TODAY()-7),B2:B5) Hope this helps! In article , arslan wrote: correction for my problem... A B C D E 1 date working hours work.hors yor work.hours for work.hours last 7 days last 30 days for last year 2 12.20.05 08:15 3 01.01.06 08:00 4 03.25.06 07:55 5 04.15.06 08:45 How can i formulate each cell on column C,D, and E? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time formulate
:) thanks, Domenic...
"Domenic": For the total hours in the last 7 days, try... =SUMPRODUCT(--(SUBSTITUTE(A2:A5,".","/")+0TODAY()-7),B2:B5) ....and format the cell as follows... Format Cells Number Custom Type: [h]:mm If you change the format so that Column A contains true dates, for example... 12/20/05 4/11/06 4/25/06 4/15/06 ....you could use the following formula instead... =SUMPRODUCT(--(A2:A5TODAY()-7),B2:B5) Hope this helps! In article , arslan wrote: correction for my problem... A B C D E 1 date working hours work.hors yor work.hours for work.hours last 7 days last 30 days for last year 2 12.20.05 08:15 3 01.01.06 08:00 4 03.25.06 07:55 5 04.15.06 08:45 How can i formulate each cell on column C,D, and E? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time formulate
In article ,
arslan wrote: :) thanks, Domenic... You're very welcome! Actually, if Column A contains true dates, you could simply use... =SUMIF(A2:A5,""&TODAY()-7,B2:B5) Hope this helps! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hot key for time? | Excel Worksheet Functions | |||
| time sheet drop down lists | Excel Discussion (Misc queries) | |||
| time entry with am/pm and no colons | Excel Discussion (Misc queries) | |||
| Entering Times | Excel Discussion (Misc queries) | |||
| time differences in a column | Excel Worksheet Functions | |||
Hybrid Mode
