Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default calculating night time

Hi all,

I have a logbook spreadsheet with hours logged in time format and the total
calculated in the third column formatted [h],mm, If I specify sunset in a
time format can I get the night hours in a seperate column?
cheers

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default calculating night time

If I understand you correctly and assuming that you have - say - sunset in
B2 and sunrise in C2 the:

=MOD(C2-B2,1)

will give you the hours of darkness.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"joe1999" wrote in message
...
Hi all,

I have a logbook spreadsheet with hours logged in time format and the
total
calculated in the third column formatted [h],mm, If I specify sunset in a
time format can I get the night hours in a seperate column?
cheers




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default calculating night time

Thanks Sandy I'll try that,
what I have is this in columns
16:30 (time out)
21:30 (time in)
5:00 (total time)

now if say sunset is at 1930 what want is another column
2:00 (night)

hope that makes more sense.


"Sandy Mann" wrote:

If I understand you correctly and assuming that you have - say - sunset in
B2 and sunrise in C2 the:

=MOD(C2-B2,1)

will give you the hours of darkness.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"joe1999" wrote in message
...
Hi all,

I have a logbook spreadsheet with hours logged in time format and the
total
calculated in the third column formatted [h],mm, If I specify sunset in a
time format can I get the night hours in a seperate column?
cheers





  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default calculating night time

If your 21:30 is in B2, and your 19:30 in D2, the formula =B2-D2 will give
2:00, or use =IF(B2-D2<0,"",B2-D2) if you want the result blank if B2 is
less than D2.
--
David Biddulph

"joe1999" wrote in message
...
Thanks Sandy I'll try that,
what I have is this in columns
16:30 (time out)
21:30 (time in)
5:00 (total time)

now if say sunset is at 1930 what want is another column
2:00 (night)

hope that makes more sense.


"Sandy Mann" wrote:

If I understand you correctly and assuming that you have - say - sunset
in
B2 and sunrise in C2 the:

=MOD(C2-B2,1)

will give you the hours of darkness.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"joe1999" wrote in message
...
Hi all,

I have a logbook spreadsheet with hours logged in time format and the
total
calculated in the third column formatted [h],mm, If I specify sunset in
a
time format can I get the night hours in a seperate column?
cheers







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Time with Date/Time checking in formula cmatera Excel Worksheet Functions 2 August 11th 08 01:38 PM
how to get "day/night" from a cell of time/hour kbee Excel Worksheet Functions 3 February 13th 08 06:48 AM
calculate finishing time if night shift aren't working Martin B Excel Worksheet Functions 1 June 20th 07 11:48 PM
calculating timesheet, time-in/time-out = total hours & minutes, . Steve Lindsay Excel Worksheet Functions 13 November 8th 06 04:45 PM
Calculating effective time from start/end date+time Stefan Stridh Excel Worksheet Functions 8 November 27th 04 04:50 PM


All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"